Loop until a certain row.

Rob*

Board Regular
Joined
Apr 23, 2010
Messages
82
Hi,

I guess this is quite simple but I can't get it.
How do I Loop a macro to row 1000?

I have: Loop Until ActiveCell = vbNullString
But as I have empty cells every now and then the macro stops.
I want the loop to go all the way to row 1000
like Loop Until Row=1000
But how do I write so excel understand what I want?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try like this

Code:
For irow = 1 To 1000
'
'do something
'
Next irow
 
Upvote 0
hmm...
It never stops.
My screen just flicker...
Code:
Sub Fixar()
    Sheets(2).Select
    Range("A2").Select
    For irow = 1 To 1000
Do
    If ActiveCell = "" Then
        ActiveCell.EntireRow.Delete
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop
Next irow
End Sub
 
Upvote 0
Try like this

Code:
Sub Fixar()
Sheets(2).Select
For irow = 1000 To 1 Step -1
    If Range("A" & i).Value = "" Then Rows(i).Delete
Next irow
End Sub
 
Upvote 0
You misunderstood what Peter suggested. You do not want to stick a Do...Loop inside, you want to use a For...Next loop, which you'll want to look up in VBA help.

Given that you are deleting rows, you'll also want to work backwards, lest you will end up skipping rows. Not tested, try:
Rich (BB code):
Sub Fixar()
Dim iRow As Long
    
    With ThisWorkbook.Worksheets("YourSheetName")
        For iRow = 1001 To 2 Step -1
            
            If .Cells(iRow, "A").Value = "" Then
                .Cells(iRow, "A").EntireRow.Delete
            End If
        Next iRow
    End With
End Sub
 
Upvote 0
Sometimes I just need to hit my forehead harder with my own palm.

It doesn't have to be harder than this.
Code:
Sub Simple()
Sheets(2).Select
Range("A1:A30").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End Sub
 
Upvote 0
Or

Code:
Sub Simpler()
Sheets(2).Range("A1:A30").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
 
Upvote 0
Lol, there is always a better way!
If I hadn't been needing all the number where they are. I could've just sort it Asc or Desc where all the empty row will be last...
That have to be more simple, not even using a macro.. :ROFLMAO:

Thanks all for helping anyway!
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top