Selecting Multiple Non-Adjacent Rows only to the End of the Data

[Poor Dave]

Trying to create myself a cheat sheet to remember syntax for selecting rows. Can someone help fill in my last blank please for selecting non-adjacent rows with a minimum code language?

I'm thinking I have to establish the range of the data on the sheet first, identify the rows containing this word, and then the syntax might need to use a form of 'UNION' to select those rows, but I don't have a clear understanding of it.

7. Selecting multiple rows.

syntax	Rows("18:27").Select	This code selects multiple adjacent rows, row 18 through 27 being the specific rows and all the way to the far, right end of the spreadsheet including all blank cells in all the columns in that row.
	Range("2:2,4:4,6:6,7:7").Select	This code selects multiple non-adjacent rows, and all the way to the far, right end of the spreadsheet including all blank cells in all the columns in that row.
	Range("BW8:BW12").Select Range(Selection, Cells(ActiveCell.Row, 1)).Select	This code selects multiple adjacent rows, just to the last column with data on the spreadsheet and includes all blank cells in all the columns in that row. (The specific last column of data is known and the specific rows that contain the data is known)
		This code selects multiple non-adjacent rows, just to the last column with data on the spreadsheet and includes all blank cells in all the columns in that row. (The specific column and row that contains the data are known, such as selecting just to the end of the data for all non-adjacent rows that contains the word CANCELED in column H)

 

[HaHoBe]

Hi Poor Dave,

have a look (and maybe a link to) SpecialCells limit problem.

This might be a start:

Public Sub MrE_1230121_1702F17()
' https://www.mrexcel.com/board/threads/selecting-multiple-non-adjacent-rows-only-to-the-end-of-the-data.1230121/
  On Error Resume Next
  With Range("A1").CurrentRegion
    .AutoFilter Field:=8, Criteria1:="CANCELLED"
    .SpecialCells(xlCellTypeVisible).Cells.SpecialCells(xlCellTypeBlanks).Select
    .AutoFilter
  End With
  Err.Clear
  On Error GoTo 0
End Sub

Ciao,
Holger

 

[Poor Dave]

Hi Poor Dave,

have a look (and maybe a link to) SpecialCells limit problem.

This might be a start:

Public Sub MrE_1230121_1702F17()
' https://www.mrexcel.com/board/threads/selecting-multiple-non-adjacent-rows-only-to-the-end-of-the-data.1230121/
  On Error Resume Next
  With Range("A1").CurrentRegion
    .AutoFilter Field:=8, Criteria1:="CANCELLED"
    .SpecialCells(xlCellTypeVisible).Cells.SpecialCells(xlCellTypeBlanks).Select
    .AutoFilter
  End With
  Err.Clear
  On Error GoTo 0
End Sub

Ciao,
Holger

Thanks H for the quick response.

Could not get this to work within my spreadsheet.

So, with your start, I utilized your idea for a filter, did a macro record and ended up with below code, since I have a known row and column end when the 'CurrentRegion' syntax is used. It worked for my sheet whether the columns already had an existing filter on or no columns filtered.

Can you please tell me what the three 'error' language syntax is for? Wandering if I can leave it out?

Dave

Sub test()
'On Error Resume Next
With Range("A1").CurrentRegion
.Select
End With
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BX$100").AutoFilter Field:=41, Criteria1:="CANCELED"
'Err.Clear
'On Error GoTo 0
End Sub

 

[HaHoBe]

Hi Poor Dave,

please use code-tags when presenting procedures here - see How to Post Your VBA Code

'The next codeline is telling the Application to continue in any case (!!!) - you will not know if command was executed or not
'On Error Resume Next

'With Range("A1").CurrentRegion
'.Select
'End With
'Selection.AutoFilter
'ActiveSheet.Range("$A$1:$BX$100").AutoFilter Field:=41, Criteria1:="CANCELED"

'may be used like
'Column 41 is definitely not Column H (would be Column 8) as in the opening post, it means Column AO
With Range("A1").CurrentRegion
  .AutoFilter Field:=41, Criteria1:="CANCELED"
End With
'On Error GoTo 0

In coding you must not follow what you see in macro recorder code: Select first to do the commands on all properties of a Selection but can work without this command to achieve faster and shorter code.

In my example the codeline On Error resume Next was there because if there are no blank cells are found in the range a run-time error will be raised - you can avoid this if you narrow down the range in question first and apply WorksheetFunction.CountBlank on the remaining cells which must retain a value greater than 0 to run the SpecialCells(xlCellTypeBlanks) successfully.

Holger