User Profile
steveccc
Brass Contributor
Joined Nov 28, 2019
User Widgets
Recent Discussions
Re: Filter rows by color, and copy/paste output using Excel VBA
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 SubFilter 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".Re: Extract different player names referring to an identical text string into separate columns.
SergeiBaklan Hi Sergei. It seems like you are using NBA (basketball) player data as an example. But for this question, we should be using the NFL (football) player instead (please see attached). Your original formula solution for NBA player data worked because each player was referenced by a unique identifier. However, your formula solution for NFL player data does not work because the NFL data uses shared identifiers for each position. Let's look at the data in Cell B2 in the attached example of NFL player data: QB Carson Wentz RB Christian McCaffrey RB Miles Sanders WR Davante Adams WR DeVante Parker FLEX Alshon Jeffery WR Dede Westbrook TE Tyler Higbee DST Chiefs On the Excel worksheet, Identifiers are in D1:L1 Here is how the data appears in D2:L2 using your formulas: Carson Wentz R Christian McCaffrey RB Miles Sanders W Christian McCaffrey RB Miles Sanders W Davant Davant Davant Adams WR DeVante Parker F Alshon Jeffery WR Dede Westbrook TE Tyler Higbee D Chiefs Obviously this is not working properly. However, if there was a way to change the Column B data so that the shared identifiers (RB, RB, WR, WR, WR) are changed into unique names (RB 1, RB2, WR1, WR2, WR3), then your original formula solution would work. In summary, some kind of find/replace solution is needed for transforming the shared identifiers (RB, RB, WR, WR, WR) in Column B into unique identifiers (RB1, RB2, WR1, WR2, WR3).
