Need help with modifying my VBA code

I need help with changing two thing in my code.

one of them is  instead of hard coded path.   
Const basePath As String = "c:\Users\Skylar\Downloads\Factory"

Open in new window


I need help to modify this, so that instead of this fixed path.  I would get a prompt to select the path from the directory. I searched google but got confused with too many different answers that i could not put to use.

the second help i need is that my current range is static from A2 to A13 i want to change is to dynamic so that if there is more value it expands the range and if the values are not there then it collapse the range.
I do not know how to change this little code below 
    Dim ws As Excel.Worksheet
    Set ws = wb.Worksheets(2)
    
    Dim rng As Excel.Range
    Set rng = ws.Range("A2:A13")

Open in new window

LVL 4
Skylar-BarlowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
For the first question, use a FileDialog, like this...

    Dim basePath As String
    Dim fd As FileDialog
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .Title = "Select Base Path"
        .ButtonName = "Select"
        .AllowMultiSelect = False
        If .Show = -1 Then
            basePath = .SelectedItems.Item(1)
        Else
            Exit Sub
        End If
    End With

Open in new window


The answer to your second question is to set your range like this...

Set rng = ws.Range(ws.Range("A2"), ws.Range("A2").End(xlDown))

Open in new window


This assumes there will be no blank cells in the desired range.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
I need help to modify this, so that instead of this fixed path.  I would get a prompt to select the path from the directory
by simply using an Input Box?

basePath = InputBox("Enter your Path", "Base Path")

Open in new window


and then do your necessary verification.

the second help i need is that my current range is static from A2 to A13 i want to change is to dynamic so that if there is more value it expands the range and if the values are not there then it collapse the range.

perhaps to make it a variable by looking into the last row.

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 Set rng = ws.Range("A2:A" & lastRow)

Open in new window


Find last row, column or last cell
https://www.rondebruin.nl/win/s9/win005.htm
Sam JacobsDirector of Technology Development, IPMCommented:
If you would like to have the folder dialog start at the location of the current file, add this to the top of your code:
Option Explicit

Function GetFolder(strPath As String) As String
Dim folder As FileDialog
Dim sItem As String
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
Dim ret As Integer
With folder
    .Title = "Select a path to the .htm files"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show = -1 Then
        GetFolder = .SelectedItems(1)
    Else
        GetFolder = ""
    End If
End With
Set folder = Nothing
End Function

Open in new window

Then replace your first line above (Const basePath As String = "c:\Users\Skylar\Downloads\Factory") with:
    Dim basePath As String
    basePath = GetFolder(Application.ActiveWorkbook.path)
    If basePath = "" Then Exit Sub

Open in new window

Skylar-BarlowAuthor Commented:
Thanks very  much to all of you.  Much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.