Simple video on how to create dynamic excel range using VBA

A simple video on how to create a dynamic excel range using VBA

Video Link: https://youtu.be/1ia8Imrk2kI

Also, check more dynamic range functions here 

Sub Get_range_lr_lc_usedRange_Tst()

'find in used range
'last row
'last column
'1st row in used range
'& = long
'tested OK
'Manually replicate; CTRL + END

Dim uLC&
Dim uLR&
Dim uFR&
Dim uFC&


uLR = ActiveSheet.UsedRange.Rows.Count
uLC = ActiveSheet.UsedRange.Columns.Count

uFR = ActiveSheet.UsedRange.Row
uFC = ActiveSheet.UsedRange.Column

Debug.Print uLR
Debug.Print uLC

Debug.Print uFR
Debug.Print uFC

End Sub

Sub LR_LC_forSpec()
Dim lc As Long
Dim lr As Long
Dim LC_2 As Long
'Manually replicate; Select last cell/column and hit ctrl up / ctrl left

lr = Sheets("Customer").Range("A" & Rows.Count).End(xlUp).Row 'lr in col a . Range("?" & ... ? = column name
     'Range("A1048576").End(xlUp).Select
lc = Cells(2, Columns.Count).End(xlToLeft).Column 'lc in row 2 . Cells(?,.... ? = Row No
End Sub

Sub xCell()
 Dim xCellPosition As Range
 Dim xWord As String
 
 xWord = InputBox("", "Enter value to search", "Start typing the value to search...")
 
 Set xCellPosition = Cells.Find(What:=xWord, MatchCase:=True)
 
 If Not xCellPosition Is Nothing Then
    Debug.Print xCellPosition.Address
    'utilize xCellPosition in ways. example below
    'xCellPosition.Row
    'xCellPosition.Column
    '...
    Else
    Debug.Print "Not found"
 End If

End Sub

Sub lr_in_selection()
Dim lr As LoadPictureConstants
lr = Selection.Row + Selection.Rows.Count - 1
Debug.Print lr
End Sub

Sub test_1()
    
End Sub



Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B36"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("F2:F36"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A1:R36")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


Sub sortmyData()
Dim lr As Long

Sheets("Customer").AutoFilterMode = False
lr = Sheets("Customer").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Customer").Select
    
    ActiveWorkbook.Worksheets("Customer").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("Customer").Sort.SortFields.Add Key:=Range("B2:B" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Customer").Sort.SortFields.Add Key:=Range("F2:F" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Customer").Sort
        .SetRange Range("A1:R" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
Sub Macro4()
'
' Macro4 Macro
'

'
    ActiveCell.SpecialCells(xlLastCell).Select 'CTRL + end
    
  
    Range("M20").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveCell.SpecialCells(xlLastCell).Select
    Range("F4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Columns("F:K").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Clear
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveCell.SpecialCells(xlLastCell).Select
    Range("E18").Select
    ActiveCell.FormulaR1C1 = "sdkf"
    Range("E18").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveCell.SpecialCells(xlLastCell).Select
    Sheets("Topic").Select
End Sub
Sub copydata()
    
    Dim lr As Long

    Sheets("Customer").AutoFilterMode = False
    lr = Sheets("Customer").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Customer").Select
    
    Range("A1:R" & lr).Copy
    
    Sheets("Sheet1").Select
    
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    
    Application.CutCopyMode = False
    
End Sub
Sub Macro6()
'
' Macro6 Macro
'

'
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Cells(1)).Select
    Cells.Select
    Range("A35").Activate
    ActiveWorkbook.Save
    Range("A1").Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
End Sub


Sub dynamic_rng_test_1()
        Dim lr&
        Dim lc As Long '&
        
        'Range("A1:R36")
        Range(Cells(1, 1), Cells(lr, lc)).Select
        
End Sub

VSTO (Visual Studio Tools for Office)
Office Add-ins Platform
Professional UI/UX
PDF Automation using VBA
RibbonX First-Class Ribbon Customization
Word Automation using VBA
PPT Automation using VBA
Custom Menu using VBA
Sharepoint Automation Using VBA
Access Database Automation using Excel VBA
SQL Server Automation Using VBA
Web Automation using VBA
Excel Password Recovery/Reset
Errors and Solutions
Plug and Play Series
Office Quick Tips
Dark theme VBE