Find the end of a column

B

Bryan Kelly

How do I find the end of a column. I recorded a macro that selects down to
the end of a column. I now want to generalize the macro. In the example
below I want to replace C1:D2418 with C1:<last cell in column what ever
row it is>.

ActiveChart.SetSourceData Source:=Sheets("data").Range( _
"C1:D2418,J1:J2418,W1:W2418,Y1:Y2418"), PlotBy:=xlColumns

Can I replace Sheets("data"). with
Sheets( <something that signifies the current sheet regardless of the
name> )?

And lastly, while editing a macro I find no operator for "Save as" meaning
save this macro under a new name and leave the current name as it is. When
I want to copy a macro to another name and make some minor edits, I must
start recording a new macro, stop that one, edit a previous one, make a
copy, then go back to the new one and replace the entire macro with the new
body. Only then can I start the edit process.

Obviously I am new a macros, but I have a bunch of data log files from my
system that I must chart and analyze in two days.
 
J

JE McGimpsey

Answers in-line:

Bryan Kelly said:
How do I find the end of a column. I recorded a macro that selects down to
the end of a column. I now want to generalize the macro. In the example
below I want to replace C1:D2418 with C1:<last cell in column what ever
row it is>.

ActiveChart.SetSourceData Source:=Sheets("data").Range( _
"C1:D2418,J1:J2418,W1:W2418,Y1:Y2418"), PlotBy:=xlColumns
Click to expand...

One way:

Dim nLastRow As Long
nLastRow = Range("C" & Rows.Count).End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("data").Range( _
"C1:D" & nLastRow & ",J1:J" & nLastRow & _
",W1:W" & nLastRow & ",Y1:Y" & nLastRow), _
PlotBy:=xlColumns


Can I replace Sheets("data"). with
Sheets( <something that signifies the current sheet regardless of the
name> )?
Click to expand...

Source:=ActiveSheet.Range( _
...
And lastly, while editing a macro I find no operator for "Save as" meaning
save this macro under a new name and leave the current name as it is. When
I want to copy a macro to another name and make some minor edits, I must
start recording a new macro, stop that one, edit a previous one, make a
copy, then go back to the new one and replace the entire macro with the new
body. Only then can I start the edit process.
Click to expand...

You can just copy and paste in the VBE, can't you? Change the name and
edit away.
 
B

Bryan Kelly

I am having a difficult time with this. JE McGimpsey wrote in part,
One way:

Dim nLastRow As Long
nLastRow = Range("C" & Rows.Count).End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("data").Range( _
"C1:D" & nLastRow & ",J1:J" & nLastRow & _
",W1:W" & nLastRow & ",Y1:Y" & nLastRow), _
PlotBy:=xlColumns
Click to expand...

I want to know what this does: Range("C" & Rows.Count).End(xlUp).Row

I don't know the methods of Range so I active the Visual Basic editor, find
the word range in a macro, highlight it and right click. I get what I call
a popup list window. The window is scrollable and has Range at the top.
Looks good so far. I right click, left click, double click, etc, but it
doesn't give me any information about Range, its methods, and parameters.

So how do I find out how to really use Range?
 
T

Tom Ogilvy

go to C65536. Then hit the end key and then hit the up arrow.

This goes up the column and stops at the last row that contains a value in
column C.

Range("C" & rows.count) resolves to Range("C65536")

Then End(xlup) does the same as hitting End, then Up arrow.

When you highlight range, hit F1 to get help.

You can also go to the object browser and find Range.
 
G

Gord Dibben

Bryan

With VBE open, find the word Range in a macro. Highlight it and hit F1 to get
a list of Properties, Methods etc.

J.E.'s Range("C" & Rows.Count).End(xlUp).Row

Takes you to the very bottom of column C then looks up to the last row with
data entered.

You could use .End(xlDown) if you knew there were no blank cells in Column C
above the last cell with data.

Gord Dibben Excel MVP
 
Top