I've written some fairly short code to delete multiple (non contiguous) rows based on certain criteria (similar I guess to holding down the CTRL key and clicking on mulitple rows). In other words, the rows could be anywhere on the sheet and not necessarily sitting beside each other. It works 90% of them and I might need a work around for the other 10% :?
In summary, I use the Range.Select method for all relevant rows and when they are selected, the Range.Delete method.
The Range takes the A1 notation, so it would need to look something like Range("1:1,3:3,56:56").Select to select rows 1, 3 and 56.
The generate the row numbers between the brackets and quotes, I keep adding to and build up a string variable called Rows_To_Delete. The condition around building up the string is a simple cell value test on columns 1 and 2 for each row that has data. It works fine and I have not included it below.
'if condition then
Rows_to_Delete = Rows_to_Delete & Row_Counter & ":" & Row_Counter & ","
If Right(Rows_to_Delete, 1) = "," Then
'remove the last comma from the string.
Rows_to_Delete = (Left(Rows_to_Delete, Len(Rows_to_Delete) - 1))
'select the rows and delete.
This Code works fine in 90% of the cases. However, if the String Rows_to_Delete goes over 256 characters in length then Range(Rows_to_Delete).Select fails miserably. Anyone know why this is? I have tested it and prior to the .Select being executed the string is correctly built and has the appropraite length - sometimes up to 400 or 500 characters long. Rows_to_Delete is defined as type "String"
Anyone know any other alternatives or good workarounds for deleting non contiguous Rows?