Hi,
I have a workbook that is connected to a CSV file, which copy the contents into a worksheet upon opening the file (column headings are also copied in). I want to find a simple way of being able to calculate how many rows are in the imported file using VBA - this value should be automatically inputted into another cell somewhere else in the spreadsheet so that I can use it with the OFFSET function to reference columns when doing SUMs, COUNTs, etc.
How do I do this? I'm currently doing it by assuming a maximum bound on the number of rows, e.g. 1000. I create a separate worksheet with values 1:1000 ranging down in Column A, then in Column B having, e.g. in B1, =IF(ISBLANK(OFFSET('Imported CSV Worksheet'!A$1, A1, 0)), 0, 1), and then doing SUM($B$1:$B$1000) to calculate the number of non-empty rows. There must however be a smarter and neater way of doing this.
Bookmarks