Forum Discussion
Filter rows by color, and copy/paste output using Excel VBA
Hello, thanks for taking a look:
Worksheet "Output" contains thousands of rows of names. In reality these names are unique, but for the attached example they are uniform. Some rows in Worksheet "Output" are highlighted, some are not.
How do I copy paste all highlighted rows on Worksheet "Output" into Worksheet "Output2", and then right below that copy/paste all non highlighted rows on Worksheet "Output" into Worksheet "Output 2"?
The total number of columns, rows, and number of highlighted rows and highlighted rows, will differ for each use. So I want the VBA to filter rows by color in Worksheet "Output", then copy, then paste into Worksheet "Output2".
Worksheet "Example-of-desired-Output2" shows how I would like the VBA to paste in data into Worksheet "Output2".
3 Replies
- Here's the answer I came up with. The key was finding a way to select a row with no data:
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Sub Macro4()
'
' Macro4 Macro
'
Sheets("Output").Select
Columns("H:H").Select
Selection.AutoFilter
ActiveSheet.Range("$H$1:$H$1000").AutoFilter Field:=1, Criteria1:=RGB(255, _
255, 0), Operator:=xlFilterCellColor
Range("A2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Output2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Output").Select
Application.CutCopyMode = False
ActiveSheet.Range("$H$1:$H1000").AutoFilter Field:=1, Operator:= _
xlFilterNoFill
Selection.Copy
Sheets("Output2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Output").Select
Selection.AutoFilter
Range("A2").Select
Sheets("Output2").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub - You know, you can click on the filter button and select sort by colour. you can do that on any column you want to focus on.
This way you don't have to use a VBA. Unless you want to do more after sorting. - Alternatively, how do I select a blank cell beneath a column of data (or a blank row beneath multiple columns)? That tidbit is really all I need to create this macro on my own, I think.
