Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locate empty cell after import of data (2003)

    Hi all,

    Here is what I have, I am importing data from a CSV using the attached code. I am in need of doing this twice for two different files. the data is formatted the same, but with different detail. I need to locate the bottom of the data (then the empty cell below) so that I can import the second file to that location.

    First, is this whole method good? and if not any suggestions?


    Thanks,
    Brad
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate empty cell after import of data (2003)

    Hans,

    I have noticed lately that you have been using the Find command to locate the last row. In the past it was common to use the code:
    Range("A65536").End(xlUP).Select

    Is the Find command more efficient or is there any other reason for using it more than the Range statment lately?
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locate empty cell after import of data (2003)

    In the first place, 65536 is the number of rows in Excel 97 - 2003, but not any more in Excel 2007. You can get around this by using

    Cells(Rows.Count, 1).End(xlUp)

    This will work correctly in all versions of Excel.

    In the second place, the method using End(xlUp) works correctly if you know that the specified column (here column A) is filled with non-blank cells up to the last row used in the table.
    However, if you're not sure which column contains the last filled cell, the method using Find works better.

    In the screenshot below, Range("A65536").End(xlUp).Row or Cells(Rows.Count, 1).End(xlUp).Row will return 6, but Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row will return 8. Of course, if I had known in advance that column B is filled to the last used row, I cound have used Range("B65536").End(xlUp).Row or Cells(Rows.Count, 2).End(xlUp).Row, but if I don't know that, the other method is better.
    Attached Images Attached Images
    • File Type: png x.png (2.7 KB, 3 views)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate empty cell after import of data (2003)

    Gotcha...
    As usual, very clearly explained!
    Cheers <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>
    Regards,
    Rudi

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate empty cell after import of data (2003)

    Hans,


    Where is the addition of '1' ? I am not clear on that...


    Brad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate empty cell after import of data (2003)

    Ahhhhhh, I see . And will this place the active cell at that found location?

    can that line be added as such:

    Sub OpenCNFile()
    '
    ' OpenCNFile Macro
    ' Macro recorded 12/12/2007 by Brad Smallwood

    With Selection.QueryTable
    .Connection = _
    "TEXT;Cocuments and Settingsgz57l1My DocumentsOpenCNs.txt"
    .TextFilePlatform = 437
    .TextFileStartRow = 6
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    Dim lngRow As Long
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) + 1

    ' insert second file to load code here...


    RefreshPivotTables

    End With
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate empty cell after import of data (2003)

    With the code as is, it stops at the row

    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) + 1

    I added the second file open below this line, but it does not get that point...


    Brad

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Locate empty cell after import of data (2003)

    You inserted the code Hans gave you into a With Structure. It will fail at this position.

    Try this... (It is untested by me...)

    Sub OpenCNFile()
    '
    ' OpenCNFile Macro
    ' Macro recorded 12/12/2007 by Brad Smallwood

    With Selection.QueryTable
    .Connection = _
    "TEXT;Cocuments and Settingsgz57l1My DocumentsOpenCNs.txt"
    .TextFilePlatform = 437
    .TextFileStartRow = 6
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With

    Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1,0).Select

    ' insert second file to load code here...

    End Sub
    Regards,
    Rudi

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Locate empty cell after import of data (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> shouldn't it be:
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)<font color=red>.row</font color=red> + 1

    "Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)" is a range object...
    Steve

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate empty cell after import of data (2003)

    Having some troubles, will work at it and reply later....

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locate empty cell after import of data (2003)

    Of course! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks, I will add a comment to my previous reply.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locate empty cell after import of data (2003)

    Edited by HansV to correct omission - thanks to sdckapr!

    You can find the last used row in a (non-empty) sheet like this:

    Dim lngRow As Long
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Then add 1 to get the row number of the first blank row beneath.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locate empty cell after import of data (2003)

    Edited by HansV to correct omission - thanks to sdckapr!

    Dim lngRow As Long
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locate empty cell after import of data (2003)

    Ok,,,,

    does not seem to like the second file open code. Seems to get past the cells.find line, then stops at the second :

    With Selection.QueryTable
    .Connection = _
    "TEXT;Cocuments and Settingsgz57l1My Documents"
    .TextFilePlatform = 437
    .TextFileStartRow = 6
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

    End With

    ' RefreshPivotTables

    End Sub



    Any ideas how to get beyond this...

    Brad

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locate empty cell after import of data (2003)

    I don't think you can open multiple files like that. I'd use Workbooks.OpenText to open each file in turn, then copy and paste to the target worksheet.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •