CODEWITHSUNDEEP
excelvba
Probs
Probs

Reputation: 353

Last row in column VBA

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!

Upvotes: 3

Views: 83994

Answers (7)

Vityata
Vityata

Reputation: 43565

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.

Upvotes: 1

William Tong
William Tong

Reputation: 475

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

Upvotes: 1

user6432984
user6432984

Reputation:

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

Upvotes: 1

Dominique
Dominique

Reputation: 17493

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

Selection.End(xlDown).Select

Upvotes: 2

InternInNeed
InternInNeed

Reputation: 159

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.

Upvotes: 5

Matt Cremeens
Matt Cremeens

Reputation: 5151

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"

Upvotes: 5

codeguy
codeguy

Reputation: 98

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

Upvotes: 1

Related Questions