excel vba last column in range
'VBA to get the last row in column A that has a value.
'Change `A` to the column you care about:
With Sheet1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'A shorter way:
LastRow = Sheet1.[A1048576].End(xlUp).Row
'And to get the row of the last cell with a value
'just above the FIRST blank cell VERTICALLY:
LastRow = Sheet1.[A1].End(xlDown).Row
'----------------------------------------------------------
'The most reliable way to get the last row with data
'for the entire sheet is to use the following function:
LastRow = LastUsedRow(Sheet1.[A1])
Function LastUsedRow(r As Range)
On Error Resume Next
r.Parent.AutoFilterMode = False
With r.Cells.Find("*", r, xlFormulas, , , xlPrevious)
LastUsedRow = 1
LastUsedRow = .Row
End With
End Function
3.89
9
Dim rRange As Range
Dim rDuplicate As Range
Set rRange = ActiveSheet.UsedRange
lFirstRow = rRange.Row
lLastRow = rRange.Rows.Count + rRange.Row - 1
lFirstColumn = rRange.Column
lLastColumn = rRange.Columns.Count + rRange.Column - 1
' New reference to current range (can be truncated)
Set rDuplicate = Range(Cells(lFirstRow, lFirstColumn), _
Cells(lLastRow, lLastColumn))
Thank you!
9
0
Are there any code examples left?
New code examples in category VBA
-
VBA 2022-03-27 20:55:02 excel vba check if all substrings in list are in string
-
VBA 2022-03-27 20:40:39 excel vba set cell value to empty
-
VBA 2022-03-27 17:05:52 vba simple quote
-
VBA 2022-03-27 14:30:19 textbox find and replace vb
-
VBA 2022-03-27 09:10:21 nested if else in vb.net
-
VBA 2022-03-27 09:10:09 excel vba string to bits
-
VBA 2022-03-25 02:00:15 vba verticalalignment
-
VBA 2022-03-24 12:20:06 Excel web scraping
-
VBA 2022-03-24 10:11:00 excel vba BitToLong
-
VBA 2022-03-21 18:00:05 excel vba save file txt