Last row in column VBA

Question

I wrote a short VBA code to automate stuff.
A short snippet is as follows:

Sub TEST()

    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$148"), , xlYes).Name = _
        "Table2"

End Sub

However, every Excel file differs with regards to the number of rows. Now when I recorded this macro it just takes the range of $A$1:$O$148. How can I adjust this part so that it automatically recognizes the last row and/or range?

I already tried:

.Range("A1").SpecialCells(xlCellTypeLastCell).Row

Instead of:

Range("$A$1:$O$148")

Thanks in advance!

Possible duplicate of [How can I find last row that contains data in the Excel sheet with a macro?](http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro) — sous2817, Sep 13 '16 at 12:51
NINJA'd You might find the page mentioned above interesting! — Skip Intro, Sep 13 '16 at 12:55

score 5 · Answer 1 · answered Sep 13 '16 at 12:54

Generally, you can find the last row / column and therefore the complete used range by using:

ActiveWorkbook.Worksheets("NAME").Range("A" & Rows.Count).End(xlUp).row

for the last row and

ActiveWorkbook.Worksheets("NAME").Cells(1, Columns.Count).End(xlToLeft).Column

for the last column. I would advice against using UsedRange because if you have blanks in between, it will lead to mistakes.

Matt Cremeens · Accepted Answer · 2016-09-13T12:53:33.200

4

This is the way I do it and I'm guessing this is a duplicate, but you can mimic hitting End-Up from a row well below your used range with

finalRow = Range("A65000").End(xlup).Row

then you can do

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$" & finalRow & ""), , xlYes).Name = _
    "Table2"

edited Sep 13 '16 at 12:53

answered Sep 13 '16 at 12:46

Matt Cremeens

4,951
7
38
67

This one was the easiest answer to follow and it worked, thanks! – Probs Sep 17 '16 at 05:38
You're welcome I believe there is value in understandable solutions. All the best in your future work. – Matt Cremeens Sep 17 '16 at 11:04
Yes there is :) Thanks! – Probs Sep 17 '16 at 15:20

score 1 · Answer 3 · answered Sep 13 '16 at 12:51

You can use the UsedRange property of your worksheet-object. You can get the indexes of your last used row and column using

ActiveSheet.UsedRange.Columns.Count
ActiveSheet.UsedRange.Rows.Count

So you would basically use this like

With m_Sheet
  ' Use this range
  .Range(.UsedRange.Rows.Count, .UsedRange.Columns.Count)
End With

score 1 · Answer 4 · answered Sep 13 '16 at 12:55

1

I used the shortcut Ctrl+Arrow Down, which resulted in following VBA (after recording the macro):

Selection.End(xlDown).Select

answered Sep 13 '16 at 12:55

Dominique

16,450
15
56
112

score 1 · Answer 5 · answered Sep 13 '16 at 13:27

1

This will do the trick:

Dim rSource As Range

With Worksheets("Sheet1")

    Set rSource = .Range("A1", .Columns("A:O").Find(What:="*", After:=.Range("A1"), SearchDirection:=xlPrevious))

End With

answered Sep 13 '16 at 13:27

score 1 · Answer 6 · answered May 24 '17 at 02:59

This would help if you don't know much of the vba library or syntax like me.

Dim lastline as long
Dim lastColumnline as long
dim usedcells as long
dim i as long
dim YOURCOLUMN as long
dim count as long

Set ws = Worksheet("blablabla")
lastline = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
usedcells = Application.WorksheetFunction.CountA(ws.Columns(YOURCOLUMN))
for i = 1 to lastline
 if ws.cells(i,YOURCOLUMN) <> vbnullstring then
  count = count + 1
     if count = usedcells then
        lastColumnline  = i
        'i is the lastColumnline 
     end if
 end if
next i

score 1 · Answer 7 · answered Nov 20 '17 at 14:53

This is how I locate last row:

Function lastRow(Optional strSheet As String, Optional column_to_check As Long = 1) As Long

    Dim shSheet As Worksheet

    If strSheet = vbNullString Then
        Set shSheet = ActiveSheet
    Else
        Set shSheet = Worksheets(strSheet)
    End If

    lastRow = shSheet.Cells(shSheet.Rows.Count, column_to_check).End(xlUp).Row

End Function

The column is optional, if no value is given, it is column A. My GitHub repository with LastThings is here.

Last row in column VBA

7 Answers7

Linked