Text import - How to set the range 2

[analyst1411]

Hi,

I created a macro to import a csv file and I want to do some extra calculations on the imported data. My problem is that the file I import gets updated and the number of records change and I need to perform the calculations on valid lines (those with data) only. How do I specify the range since it varies every time?

Thanks,
Analyst1411

 

[firefytr]

Hi there,

Depending on how your data is setup, here are some examples ...

Sub FindLast_Example1()

    Dim wb As Workbook, ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    LastRow = ws.Cells.Find("*", after:=ws.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    LastCol = ws.Cells.Find("*", after:=ws.Cells(1, 1), searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

End Sub

Sub FindLast_Example2()

    Dim wb As Workbook, ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlUp).Column

End Sub

The difference between example 1 and example 2 is, in example 1 it will find the last value no matter what, the second one will ignore hidden rows/columns and will be looking at only one location (column A on the LastRow, row 1 on the LastCol).

HTH

-----------
Regards,
Zack Barresse

 

[SkipVought]

Hi,

If you use Data/Import Data...

you can CHECK a box Fill down formulas in columns adjacent to data

Then all your formulas will fill to the number of rows imported when you Data/Refresh.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}