Posts by gijsmo

  • VBA is searching under US Date range and not UK.

    I'm not quite sure what you mean by "I’ve noticed the dates must be sorted first for it to be effective otherwise it only copies data over up to a point."

    Auto-filtering should not need data to be sorted first. I think you'll need to provide more information about what you're seeing or perhaps another example workbook as the sample one only has a very limited number of rows.


    And, yes, you can of course cut & paste to the new workbook and remove the "empty" rows with more code.

  • VBA is searching under US Date range and not UK.

    Having dealt with the many issues relating to dates when not using the American date format, this is a very common issue.


    For auto-filtering, best to convert your input date strings into "proper" dates and then into longints as underneath the date format, Excel stores dates as a number.


    Obviously, adding whatever validation routine/s you like to confirm the input dates entered are valid is a good idea - and yes, you would have to add some error checking as a result. However, assuming that dates are entered correctly as dd/mm/yy (or dd/mm/yyyy) strings then the below changes work fine on my version of Excel (I'm in Australia, so dd/mm/yyyy is the default format here).


    Note that I've also tinkered with the filter range in the code snippet below because the code was not using the header row (row 2) in filtering. This meant the row 3 was pretty much copied every time irrespective of the start/end date entered.


  • Sorting 10 rows without affecting other rows

    Maybe try the following.

    Note that this has been set up to sort the whole row of the table (using K as the last column in this example)


  • excluding folder paths with spaces

    The code I suggested in my previous post will fix the issue of the "\" on the end of the parent folder - if you don't add it to the parent folder in cell A1, the code will simply add it for you. This seems to work OK for all the test cases I've created at my end.


    Your other questions are now going well beyond the original problem and probably require a new thread.

  • excluding folder paths with spaces

    The problem I think is not including the final backslash in the parent path, this should be fixed by adding this line in the ListMyFiles sub:

    Code
    1. If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"

    best place is probably here:

    Code
    1. Set myObject = New Scripting.FileSystemObject
    2. If Right(mySourcePath, 1) <> "\" Then mySourcePath = mySourcePath + "\"
    3. Set mySource = myObject.GetFolder(mySourcePath)

  • Why is this VBA code causing rows to unhide?

    If the rows were being hidden as a result of using one or more filter conditions on the V40:AF417 range then you are completely clearing that filter with the following code:

    Code
    1. .AutoFilter

    which in turn removes any rows previously hidden by the filter selection

  • excluding folder paths with spaces

    It looks like this line in ListMyFiles

    Code
    1. sf = LCase(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath) - 1))

    is truncating the first letter of the folder, also it is converting the result to lower case


    Then the IndexStrArray function is comparing a lower case string to whatever you typed into cell A3


    Try changing to:

    Code
    1. sf = Trim(Right(mySubFolder.Path, Len(mySubFolder.Path) - Len(mySourcePath)))

    The 'Trim' part isn't strictly necessary but won't hurt.


    and then in the IndexStrArray function change the compare line to:

    Code
    1. If LCase(vArray(i)) = LCase(sVal) Then

  • Find function in VBA

    To avoid skipping over the first value when using Find, set the After parameter to last cell in the range eg:


  • VBA to force save excel document as macro-enabled & auto populating filename as reference in a cell

    Not sure what the purpose is of asking the user to specify a filename in the following code:

    Code
    1. xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")

    It just seems to be a trigger to confirm saving the file.

    xFileName is not used in the code, the following code overrides whatever filename is chosen with the value of ThisFile:

    Code
    1. ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    The only thing I can see that GetSaveAsFilename will do is maybe change the destination folder of the saved file.

  • Data input from user form not inserted into excel

    Without changing loads of code you've already created, one way to do this could be as follows in place of your "For" loop in the AddRecord routine.