Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Define My Dynamic Range Start Cell by Input box

0

Dear Sirs,

In the following dynamic range found in the link below

https://www.thespreadsheetguru.com/blog/5-ways-to-create-a-dynamic-auto-adjusting-vba-range

The start Cell already set at Range("D9")

Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")
Set StartCell = Range("D9")

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

End Sub

I need help in modifying the code to define StartCell by an input box.

Looking forward to having your further help in this regards

Many thanks and best regards

Arsil Hadjar

Answer
Discuss

Answers

0
Selected Answer

The code below was modified to allow you to input a cell address from which to start a selection which extends as far down as there are data in the specified cell's column and as far to the right as there are data in the same cell's row.

Sub DynamicRange()
    ' select the used range to the right and bottom of the sheet
    ' starting from the given StartCell

    Dim LastRow As Long
    Dim LastColumn As Long
    Dim StartCell As String

    StartCell = InputBox("Enter coordinates for determining the used range:", _
                         "Start cell address", "A1")
    With Worksheets("Sheet1")
        'Find Last Row and Column
        LastRow = .Cells(.Rows.Count, Range(StartCell).Column).End(xlUp).Row
        LastColumn = .Cells(Range(StartCell).Row, .Columns.Count).End(xlToLeft).Column

        'Select Range
        .Range(.Range(StartCell), .Cells(LastRow, LastColumn)).Select
    End With
End Sub

I think this is a cumbersome process. Therefore I offer an alternative. Instead of entering a cell addresss, select the cell the address of which you would have entered. The run the code below and the same range will be selected.

Sub DynamicRange2()
    ' select the used range to the right and bottom of the sheet
    ' starting from the selected StartCell

    Dim LastRow As Long
    Dim LastColumn As Long

    With ActiveSheet
        'Find Last Row and Column
        LastRow = .Cells(.Rows.Count, ActiveCell.Column).End(xlUp).Row
        LastColumn = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column

        'Select Range
        .Range(ActiveCell, .Cells(LastRow, LastColumn)).Select
    End With
End Sub
Discuss

Discussion

Dear Variatus,

Many thanks for the help and the alternative methods Instead of entering a cell address, select the cell the address of which I would have entered.

Both work Ok.

Best regards
Arsil Hadjar
Arsil (rep: 32) Jun 26, '19 at 4:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login