I know this topic comes up all the time, but I think my situation is slightly different than some others.
First, let me say I am a novice at writing VBA macros, but I get by with my code for the most part, despite it likely being inefficient.
I have written a macro for a database dump that can be of any number of rows long (but by no means would it ever be greater than 5,000 rows). Thus, I've applied some formula logic to my macro that copies the formulas down from rows 2 through 5,000 (Row 1 is a header). The formulas check the first column (an identifier number) to determine if there is data there or if it is blank then the formula returns "" (blank).
At the end of the macro I do a copy/paste values to remove the formulas from the final product. However, Excel still thinks there is something going on all the way down to row 5,000, even if the "true" data stopped at row 2,500. This creates an issue when I take this data and do an import into another system - it views all the blank rows as "BAD" records.
I've tried adding "delete last used row" logic but Excel still maintains that the blank cells through 5,000 are "populated" and thus if I tested my data with a SelectALL, everything from A1 through W5000 would be selected.
Here is a sampling of how my formulas are currently written
First, let me say I am a novice at writing VBA macros, but I get by with my code for the most part, despite it likely being inefficient.
I have written a macro for a database dump that can be of any number of rows long (but by no means would it ever be greater than 5,000 rows). Thus, I've applied some formula logic to my macro that copies the formulas down from rows 2 through 5,000 (Row 1 is a header). The formulas check the first column (an identifier number) to determine if there is data there or if it is blank then the formula returns "" (blank).
At the end of the macro I do a copy/paste values to remove the formulas from the final product. However, Excel still thinks there is something going on all the way down to row 5,000, even if the "true" data stopped at row 2,500. This creates an issue when I take this data and do an import into another system - it views all the blank rows as "BAD" records.
I've tried adding "delete last used row" logic but Excel still maintains that the blank cells through 5,000 are "populated" and thus if I tested my data with a SelectALL, everything from A1 through W5000 would be selected.
Here is a sampling of how my formulas are currently written
Code:
Range("X2").Select
Selection.NumberFormat = "m/dd/yyyy"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-23]="""","""",IF(RC[-19]="" - -"","""",RC[-19]))"
Range("Y2").Select
Selection.NumberFormat = "m/dd/yyyy"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-24]="""","""",IF(RC[-19]="" - -"","""",RC[-19]))"
Range("Z2").Select
Selection.NumberFormat = "m/dd/yyyy"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-25]="""","""",IF(RC[-19]="" - -"","""",RC[-19]))"
Range("AA2").Select
Selection.NumberFormat = "m/dd/yyyy"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-26]="""","""",IF(RC[-19]="" - -"","""",RC[-19]))"
Range("X2:AA2").Select
Selection.AutoFill Destination:=Range("X2:AA5000"), Type:=xlFillDefault
