Looking to select noncontiguous rows with For loop

[Bishop]

I have the following code:

For i = 13 To (ZeroRow - 7) Step 8
.Rows(i).Select
Next
.Selection.Delete

What I'm trying to do is select ALL rows from 13 to (ZeroRow - 7) THEN
delete them all at once. With the code written the way it is only the very
last row selected will be deleted. How do I get a "hold the control button
and select multiple rows" kind of selection using VBA code?

 

[r]

Sub Test_row_select()

Dim ZeroRow As Long, i As Long
Dim Rng As Excel.Range
With ActiveSheet
ZeroRow = 1000
For i = 13 To (ZeroRow - 7) Step 8
If TypeName(Rng) = "Nothing" Then
Set Rng = .Cells(i, 1).EntireRow
Debug.Print Rng.Address
Else
Set Rng = Application.Union(Rng, .Cells(i, 1).EntireRow)
End If
Next
Rng.Select
End With
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Rick Rothstein]

Your question... the Step 8 part coupled with your statement "select ALL
rows from 13 to (ZeroRow - 7)"... is kind of confusing. However, I would
point out that you do not have to physically select a range in order to
delete it. This statement will delete ALL rows between (and including) 13
through ZeroRow-7...

Range("A13:A" & (ZeroRow - 7)).EntireRow.Delete

Note: Since we are deleting the entire row, it doesn't matter which column
we choose a cell from on that row in order to extend it through to the
entire row, so I used Column A.

 

[Patrick Molloy]

rows(i).Delete

works too

Your question... the Step 8 part coupled with your statement "select ALL
rows from 13 to (ZeroRow - 7)"... is kind of confusing. However, I would
point out that you do not have to physically select a range in order to
delete it. This statement will delete ALL rows between (and including) 13
through ZeroRow-7...

Range("A13:A" & (ZeroRow - 7)).EntireRow.Delete

Note: Since we are deleting the entire row, it doesn't matter which column
we choose a cell from on that row in order to extend it through to the
entire row, so I used Column A.

 

[Rick Rothstein]

Well, to do it all at once like the code line I posted (again, ignoring the
Step 8 increment which I still don't see how it applies), you would need
something like this...

Rows(13).Resize(ZeroRow - 19).Delete

where the 19 would be a combination of the 7 that the OP wanted to subtract
from the ZeroRow value plus one to make the subtraction include the row at
both ends of the interval). I was going to post this solution, but figured
the Range method I presented would be easier to understand and remember in
future situations where it may be needed.

 

[Bishop]

I suppose the way I worded it is a little confusing. What I mean when I say
"ALL" rows is every 8th row starting with row 13 and ending with row (ZeroRow
- 7). I just assumed that seeing "noncontiguous" in the subject line and
seeing "Step 8" in my code one would "assume"... Of course, we all know what
happens when one assumes What meant was I wanted to select every 8th row
between 13 and (ZeroRow - 7) and delete them ALL at once. Sorry for the
confusion.

 

[Rick Rothstein]

I don't think you gain anything trying to select all the rows first (Union's
can become slow as the numbers increase); just delete them as you go (but
run the loop backwards). Give this a try (on a copy of your worksheet to be
safe)...

Sub RemoveRows()
Dim ZeroRow As Long, X As Long
Const Srow As Long = 13 ' Start row
Const Incr As Long = 8 ' Increment amount
With ActiveSheet
ZeroRow = 1000
For X = Srow + Incr * ((ZeroRow - Srow - 6) \ Incr) To Srow Step -8
.Rows(X).Delete
Next
End With
End Sub

 

[Patrick Molloy]

this woild be very fast anyway, but you might add
Application.ScreenUpdating = False
before the loop

 

[Rick Rothstein]

Yes, good idea (and, of course, setting it back to True again at the end of
the loop)... seems like when I post at 4:30 in the morning just before going
to sleep, I tend to forget things like this... thanks for catching this.

 

[Bishop]

Thank you both very much for your help. I've posted my code below to show
you what I ended up going with. Works as intented. My code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long

With Sheets("Tally Sheet")
.Cells.Copy
.Paste Destination:=Worksheets("DirectorCopy").Range("A1")
End With

With Worksheets("DirectorCopy")
.Shapes("LazyEyeButton").Cut
For j = 1 To 64
.Shapes("Done! " & j).Cut
Next
.Columns("G:G").Delete
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
'Find the last PF
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
.Rows(ZeroRow & ":515").Delete
For i = (ZeroRow - 7) To 13 Step -8
.Rows(i).Delete
Next
.Rows("6:6").Select
ActiveWindow.FreezePanes = True
End With
End Sub

BTW, the line

..Rows(ZeroRow & ":515").Delete

seems to hang for some reason. Any idea why deleting rows would do this?
Is there a more effective way to code it?

 

[Patrick Molloy]

congrats

you might try


..Range(ZeroRow & ":515").Delete

i can't see why it would cause excel to hang -- unless there's a huge bunch
of formulae that will be recalculating as a result?

 

[Bishop]

That did the trick! Thanks.

congrats

you might try


.Range(ZeroRow & ":515").Delete

i can't see why it would cause excel to hang -- unless there's a huge bunch
of formulae that will be recalculating as a result?