0

I'm not very Excel savvy, much less coding savvy but at the moment, I'm tasked with setting up an Excel record sheet of data, with such data pulled from fillable PDFs that are merged into a .csv file.

I want to have a simple command that will pull that data with the following mechanism/command path in Excel: Data > Grab Data from Text > select .csv file > delimited > comma delimited > ... > destination = next blank cell > Finish/Okay.

What I'd like help with is to have the Macro input the next .csv file data into the next empty cell in column A to ultimately create a growing list.

As of right now, I've recorded the Macro and figured if I could change the destination in line 7 from "Destination:=Range( _$A$1) to select the next blank cell instead, I'd be good.

For the CSV file titled "combined.csv", I have a command script someone helped me with that would basically rewrite it with new data each time under the same file name, which won't be a problem for us. 

Sub Macro5()

 Macro5 Macro

    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Alvin_2\Desktop\CSV Files\combined.csv", Destination:=Range( _
        "$A$1"))
        .CommandType = 0
        .Name = "combined"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

So far, I have had no luck with finding a function to select the next blank cell in column A.

Would anyone have an idea of the code I should enter in the Destination section to get the Macro to input the new data onto the next empty cell in column A?

Alvin Huynh
  • 1
  • 1
  • 1
    Possible duplicate of [Error in finding last used cell in VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba). See this question for how to find the last row in column A, which looks like what you need. – BigBen Apr 11 '19 at 19:48

1 Answers1

0

Try:
Range("A1").End(xlDown).Offset(1,0)

BrettBlade
  • 82
  • 7
  • 1
    See my comment above and the linked question. To be honest, `End(xlDown)` is not reliable. "What would happen if there was only one cell (A1) which had data? You will end up reaching the last row in the worksheet!" – BigBen Apr 11 '19 at 20:03
  • BrettBlade Thanks! That one works for my needs. I just had to remove CommandType = 0 from the Macro after a bit of digging due to an error, but did exactly what I needed! @BigBen I'm liking the information for finding the last-used cell that you provided. While Brett's answer solves my current problem, I'm thinking in the long-run and fro future setups on new files, it'd be good to consider what you linked me as well. My question: Would I put those codes as my Destination? They all seem like separate commands. I'll have to look at it more in depth tonight. – Alvin Huynh Apr 11 '19 at 23:57