VBA question about the "end down" command

MIDBOND

New Member
Joined
Oct 15, 2002
Messages
10
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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. :)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

Many men eat, but fu manchu! :)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

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! " :LOL:

Dan
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.
MIDBOND said:
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

Active Member
Joined
Jun 17, 2003
Messages
491
Code:
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. :biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,068
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top