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

 

[just_jon]

Try --

Dim LastRow as Long
LastRow = Range("A65536").End(xlUp).Row

Variable LastRow will give you the last used row in the referenced range, as we've selected the Row property of the cell. by adding 1 to it, you would then have the 1st unused cell.

Sometims the macro recorder can be verbose; but, it's a case of its recording everything it does, literally. Does this help?

 

[NateO]

Another example along the lines of Jon's:

<pre>Sub blah()
<FONT COLOR="#00007F">With</FONT> Range([a1], [a65536].End(xlUp))
.<FONT COLOR="#00007F">Select</FONT> <FONT COLOR="#007F00">'do Something here</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">With</FONT>
<FONT COLOR="#00007F">End</FONT> <FONT COLOR="#00007F">Sub</FONT></pre>

I'm not recommending to select ranges to perform actions, it's just an example.

 

[just_jon]

Whoa! Face hair!

 

[NateO]

Many men eat, but fu manchu!

 

[just_jon]

 

[HalfAce]

Hi MIDBOND, welcome to the board.
Both Jon & Nate's replies will work great for you depending on your situation.
Just as a little more explanation, let me offer the following.
The command " Range("A65536").End(xlUp).Select " will (as you've probably figured out) select the lowest used cell in column A, starting from the bottom and working its way up. If, however, you have multiple ranges (in a verticle fashon) that populate column A you may want to stick with your original idea of working your way down from a specified point. For example, say you've got data in ranges from A3 to A53, from A63 to A103 and from A110 to A200. Now you want to select the lowest used cell in the middle range (A63:A103). For this you would want to use:
Range("A63").End(xlDown).Select. This will put you in the lowest used cell after A63, (where the next cell down is empty) regardless of the fact that you have data in column A below that.
I don't remember how you would do that in 123. (Been too many years.)
If you wanted to select the first empty cell below A63, you just add an offset statement. ( Range("A63").End(xlDown).Offset(1, 0).Select )

Does any of this help?

Jon,
I would think, judging from the pictures of you and Nate, you response
would just be " Whoa! HAIR! "

Dan

 

[just_jon]

 

[tusharm]

The End method (property?) is the most common way of doing what you want. However, there are rare cases when it will fail. You might want to check http://www.mrexcel.com/board2/viewtopic.php?t=46684&highlight=enhanced for more. Also, and as Nate pointed out, in the vast majority of cases there is no need to Select or Activate anything. For more, see the Excel/VBA/'Beyond Excel's recorder' page of my web site.

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

 

[north19701]

Sub DeleteBlanks()
Range([a1], [a65536].End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Instead of looping through your range, the above would delete the rows where the cells in column A is blank.

Nateo - I once tried the fu manchu - lasted a week before my girlfriend (now wife) decided to deny certain nightly activities until it was gone...wear it with pride for all those who can't.