How to set the last data row excel macro

Question

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

The most detailed thread I've come across so far:https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — Stax, Jul 01 '20 at 00:49
i found something like `count` but i really don't know how to insert in the range, because i don't have a last range, so i don't know how to define it — Black Mamba, Jul 01 '20 at 00:51
To "construct" a range like in your 8th line that ends at the last row, Create a Variable and save the row number of the last row +1 into it (my previous comment should help with this) then use that variable in `Range("I3:N" & EndRowNumber).Select`. By the way, the "general" form of this is `Range(StartColumnLetter & StartRowNumber & ":" & EndColumnLetter & EndRowNumber).Select`. There are other less clunky ways of working with ranges, but just helping with what you've got already. — Stax, Jul 01 '20 at 01:12

score 2 · Accepted Answer · answered Jul 01 '20 at 01:58

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)

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

How to set the last data row excel macro

1 Answers1