VBA Snippets

Post #519 written by Khodok in Code

Content

These might someday be useful again

VBA - Dynamic Range
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
Dim ws As Worksheet
Set ws = Sheets("SOMESHEET") ' Rename the sheet

Dim lastRow As Integer ' For optimisation, don't forget to change it to Byte (255), Integer (32,767) or Long (2B) depending on how many lines you have
Dim lastColumn As Byte ' For optimisation, don't forget to change it to Byte (255), Integer (32,767) depending on how many columns you have
Dim dynamicRng As Range

' Find the last non-blank cell in column A (1)
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Find the last non-blank cell in the first row (assuming data starts in row 1)
' Change "A1" to where your data starts if it's different
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

' Define the range from A1 to the last non-blank cell in the last row and column
Set dynamicRng = ws.Range("A1").Resize(lastRow, lastColumn)
Comments

Please Log in to leave a comment.

No comments yet.