This is a VBA question. First of all I am not a programmer, but I am a fairly sophisticated user. I have written lots of macros in 123 but I’m new to VBA.
The question: I have a daily data file that I get from an external source. The data file will vary in length from day to day. It has a number of blank lines that I want to delete. I have set up a macro to clean up the data file but I’m running into a problem programming the deletion of the number of lines. The macro is able to sort the table into lines with data and lines without. Once this is done I go to the top of a given column and use the command (end down) to go to the bottom of the lines I want to keep. In 123’s macros this would take me to the last line of the data regardless of what line it is on. However when I try to do the same thing with VBA by recording my keystrokes the macro seems to hard code the exact address that it goes down to the first time. Here is an example of my problem. While this works fine for the first file I run, it causes problems once I use it for another file of different size. It simply keeps going to a129, even if this is not the correct line.
Selection.End(xlDown).Select
Range("A129").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("969:969").Select
I would like to make this macro as “variable” as possible. Does anyone have any ideas?
Many thanks to all
The question: I have a daily data file that I get from an external source. The data file will vary in length from day to day. It has a number of blank lines that I want to delete. I have set up a macro to clean up the data file but I’m running into a problem programming the deletion of the number of lines. The macro is able to sort the table into lines with data and lines without. Once this is done I go to the top of a given column and use the command (end down) to go to the bottom of the lines I want to keep. In 123’s macros this would take me to the last line of the data regardless of what line it is on. However when I try to do the same thing with VBA by recording my keystrokes the macro seems to hard code the exact address that it goes down to the first time. Here is an example of my problem. While this works fine for the first file I run, it causes problems once I use it for another file of different size. It simply keeps going to a129, even if this is not the correct line.
Selection.End(xlDown).Select
Range("A129").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("969:969").Select
I would like to make this macro as “variable” as possible. Does anyone have any ideas?
Many thanks to all