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? — sous2817
– sous2817, Commented Sep 13, 2016 at 12:51
NINJA'd You might find the page mentioned above interesting! — Skip Intro
– Skip Intro, Commented Sep 13, 2016 at 12:55

Matt Cremeens · Accepted Answer · 2016-09-13 12:53:33Z

5

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, 2016 at 12:53

answered Sep 13, 2016 at 12:46

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Probs Over a year ago

This one was the easiest answer to follow and it worked, thanks!

Matt Cremeens Over a year ago

You're welcome I believe there is value in understandable solutions. All the best in your future work.

Probs Over a year ago

Yes there is :) Thanks!

InternInNeed · Accepted Answer · 2016-09-13 12:54:10Z

5

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.

answered Sep 13, 2016 at 12:54

InternInNeed

15912 bronze badges

Comments

Dominique · Accepted Answer · 2016-09-13 12:55:21Z

2

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

Selection.End(xlDown).Select

answered Sep 13, 2016 at 12:55

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Comments

codeguy · Accepted Answer · 2016-09-13 12:51:04Z

1

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

answered Sep 13, 2016 at 12:51

codeguy

985 bronze badges

Comments

user6432984 · Accepted Answer · 2016-09-13 13:27:46Z

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, 2016 at 13:27

user6432984

Comments

William Tong · Accepted Answer · 2017-05-24 02:59:20Z

1

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

answered May 24, 2017 at 2:59

William Tong

4657 silver badges14 bronze badges

Comments

Vityata · Accepted Answer · 2017-11-20 14:53:30Z

1

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.

answered Nov 20, 2017 at 14:53

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Last row in column VBA

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related