CODEWITHSUNDEEP
excelvbaexcel-formula
Black Mamba
Black Mamba

Reputation: 245

How to set the last data row excel macro

I'm trying to make a macro to help me in format some files, but each file has different number of rows (but always same number of columns). I defined the last range as 99999 because i don't know how to make the macro recognize the last row with some data and stop there.

Someone can help?

Thank you

  Rows("1:26").Select
    Selection.Cut
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Range("C16").Select
    Sheets("teste").Select
    Selection.Delete Shift:=xlUp
    Range("I3:N99999").Select
    Selection.Cut
    Range("I1").Select
    ActiveSheet.Paste
    Range("P3:P99999").Select

Also tried and had success:

    Dim Nb_Rows As Integer
Nb_Rows = WorksheetFunction.CountA(Range("H:H"))
For i = 1 To Nb_Rows
Range("I" & i).Value = Range("H" & i).Value + Range("F" & i).Value
Next i

Upvotes: 0

Views: 1962

Answers (1)

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

You can use something like this to find the last row

lastRow = Range("N" & Rows.Count).End(xlUp).Row

Then use string contatenation to use lastRow in the range object:

rangeIN = Range("I3:N" & lastRow)

enter image description here

Here is the code I used for this sample program:

Sub SelectRange()
    Dim rangeIN As Range
    lastRow = Range("N" & Rows.Count).End(xlUp).Row
    Set rangeIN = Range("I3:N" & lastRow)
    rangeIN.Select
End Sub

What exact code you will want to use though depends on the circumstances. For instance, if you know that there will always be at least one line of data in the N column, if the last row may be in another column (like M), etc. So it depends on a few variable factors which you will have to test out to make your macro work reliably. I recommend checking out this question for more info, as suggested by Stax.

P.S. you don't need to select a range to copy and paste it. This is a common misconception and adds inefficiency. You can usually use something like this instead:

Sheet2.Range("A1:B2").Value2 = Sheet1.Range("A1:B2").Value2

Upvotes: 2

Related Questions