VBA question about the "end down" command

[MIDBOND]

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

 

[StrugglingToCrackIt]

You may well shoot me down in flames for this, more experienced users!, but I have had to write a number of variable length list macros and I had the same problem with the hardcoding/end-down that was mentioned. I found a way around it was to use a do while loop -

dim counter as integer 'optional if need to know list length as well as row number

dim lastrow as double

range("a5").select

do while isempty(activecell)=false
activecell.offset(1,0).select 'moves you down the column
counter=counter+1 'optional see below
loop
lastrow=activecell.row

it helps to put in a counter sometimes, if you then need to select the entire range or use it in further programming later on in the macro. eg range("A5","A"&counter+5).select or the counter itself can be used to drive a further loop.

I appreciate that many people find using loops is slow and consider that it is basic programming, but in this instance it is very effective IMHO.

Hope this helps?