CODEWITHSUNDEEP
vbaexcel
worldCurrencies
worldCurrencies

Reputation: 467

Looping with different number of rows?

I have this code that works properly, however, the range is from I2:I800, which in some sheets there are less than 800 lines, so excel will fill the remaining ones with "0". Sometimes there are more than 800 line. What loop will help me just fill in as many rows there are?

With Worksheets("Sheet1").Range("I2:I800")
    .FormulaR1C1 = "=RC[-1] & TEXT(RC[-2],""00"")"
    .Value = .Value
End With
Worksheets("Sheet1").Columns("G:H").Delete

With Worksheets("Sheet2").Range("I2:I800")
    .FormulaR1C1 = "=RC[-1] & TEXT(RC[-2],""00"")"
    .Value = .Value
End With
Worksheets("Sheet2").Columns("G:H").Delete

Upvotes: 0

Views: 53

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

No loop needed just find the last row.

Dim LstRow as Long
LstRow = Worksheets("Sheet1").Cells(Rows.Count,8).End(xlup).Row

Then use that in your references

Dim LstRow as Long
LstRow = Worksheets("Sheet1").Cells(Rows.Count,8).End(xlup).Row
With Worksheets("Sheet1").Range("I2:I" & LstRow)
    .FormulaR1C1 = "=RC[-1] & TEXT(RC[-2],""00"")"
    .Value = .Value
End With
Worksheets("Sheet1").Columns("G:H").Delete

LstRow = Worksheets("Sheet2").Cells(Rows.Count,8).End(xlup).Row
With Worksheets("Sheet2").Range("I2:I" & LstRow)
    .FormulaR1C1 = "=RC[-1] & TEXT(RC[-2],""00"")"
    .Value = .Value
End With
Worksheets("Sheet2").Columns("G:H").Delete

Upvotes: 3

Related Questions