CODEWITHSUNDEEP
vbaexcel
Robby
Robby

Reputation: 827

Excel - selecting range until 5 empty rows

On a spreadsheet that works as an order form, the data begins on row 84. From there, there can be as few as 1 row of data or as many as thousands. Also, sometimes there will be blank rows within that data.

I have many instances of statements like this in my VBA codes:

Range("A84:X1000").Select

The problem is that sometimes that range goes much lower than it needs to be, and other times it's not far enough. Is there a way to make a range start in a specific cell (A84 in this case) and end only after it reaches 5 blank rows?

To give an example of when I need to use it, here's one bit of VBA code.

Sub FixNumbers()
Range("D84:D1000").Select
Dim c As Range

Selection.NumberFormat = "General"
For Each c In Selection.Cells
  If (c > 99) And (c Mod 50) = 0 Then c = c + 1
Next
End Sub

Upvotes: 0

Views: 377

Answers (2)

Pierre
Pierre

Reputation: 1046

taking previous answerer's idea:

Sub FixNumbers()
with Range("D84:D" & rows.count)
    Dim c As Range
    .NumberFormat = "General"
    For Each c In .Cells
        If (c > 99) And (c Mod 50) = 0 Then c = c + 1
    Next
end with
End Sub

Upvotes: 2

Preston
Preston

Reputation: 8187

Why not use the xlUp method? This simulates you selecting bottom row and pressing ctrl + up:

Set rngOppos = Sheets("Sheet1").Range("X84", Sheets("sheet1").Range("X" & rows.count).End(xlUp))

Upvotes: 1

Related Questions