leaving cells blank in a formula

[mgovatos]

I have just recorded a macro which helps me format data in an excel sheet so I can export that data to a Filemaker Pro file.

One of the functions I use in the formating is "IF(I3="twelfth",12,IF(I3="eleventh",11,IF(I3="tenth",10,IF(I3="ninth",9,""))))". This basically writes 9 for the word "ninth", 10 for "tenth" etcetera.

I created the macro for a excel sheet with 97 rows and now I am trying to use it on an excel sheet with fewer rows (ex. 72). The macro works perfectly, but when I export the data from the 72 row excel spreadsheet, the Filmaker Pro files shows 97 total records. I only want 72 to show.

I have to do this with about 100 spreadsheets all having different numbers of rows. How can I change my formula or macro so that I get blank cells to show up?

Any help would be appreciated.

 

[Andrew Poulsom]

The macro recorder always records exactly what you do, so the 97 rows will always be selected and processed.

You need to amend the code so that only your 72 (or whatever) rows are selected.

To select down to the last row in column A use this code:

Range("A1:A" & Range("A65536").End(xlUp).Row).Select

Amend the column references or the start row to suit.