VBA help on amending old code

[Anand307]

Hi All, looking for a issue to be resolved in VBA.

I have a VBA code to open the files in the folder and count the number of used rows and get the result in a new sheet.The problem is I am not getting the exact count of used rows, it is showing two count less, where in it has to just ignore header count and get the remaining ( But we see VBA is getting total used rows -2 in output sheet). I tried to amend the codes, but could not figure out exact line to amend. Please somebody help here.



Attached book Sample contains the code and sheet 1 of samework book contains data to test on for your convenience . Looking for a help, thanks in advance.

 

[Chihiro]

My guess is this line.

Const FirstDataRowInSourceFile As Long = 3

Change it to 2 and see what happens.

 

[Anand307]

Chihiro, thank you very much it worked fine. One more thing, is it possible to add the folder browse option to run the macro on user preferred folder.

 

[Chihiro]

Sure, use "Application.FileDialog" object.

Added following portion to your code.

    Dim fd As FileDialog
  
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  
    With fd
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        .InitialFileName = "C:\Test\" 'Change this to initial folder you want
        If .Show <> -1 Then GoTo NextCode
        MyPath = .SelectedItems(1) & "\"
    End With

NextCode:
    MyPath = MyPath
    If MyPath = "" Then Exit Sub

See attached as well.

 

[Anand307]

Thanks Chihiro. Really helped in understanding code better, I was trying the function to open the folder, but something was stopping from running the rest of codes after opening folder. Thanks for fix, really helped.

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

 

[Chihiro]

That's function from link below?
http://www.mrexcel.com/forum/excel-questions/294728-browse-folder-visual-basic-applications.html

You'd use it by calling the function in your code. Something like...
GetFolder ("C:\Test\")

Note that you need closing "\" for it to work. Basically it will select folder inside function as starting folder and will return picked folder once you select folder in the dialog.

 

[Anand307]

I tried to use the code from link -http://stackoverflow.com/questions/21831669/how-to-count-number-of-rows-and-to-move-files-automatically-with-vba-macros

Trying to understand more on your explanations as i am very new to VBA.