Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the end of a column
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. -- Thank you, Bryan Kelly Time is the medium we use to express out priorities. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the end of a column
Answers in-line:
In article , "Bryan Kelly" wrote: 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 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 )? 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. You can just copy and paste in the VBE, can't you? Change the name and edit away. 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the end of a column
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 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? "JE McGimpsey" wrote in message ... Answers in-line: In article , "Bryan Kelly" wrote: 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 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 )? 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. You can just copy and paste in the VBE, can't you? Change the name and edit away. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the end of a column
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. -- Regards, Tom Ogilvy "Bryan Kelly" wrote in message ... 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 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? "JE McGimpsey" wrote in message ... Answers in-line: In article , "Bryan Kelly" wrote: 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 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 )? 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. You can just copy and paste in the VBE, can't you? Change the name and edit away. 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the end of a column
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 On Sun, 13 Jun 2004 11:29:55 GMT, "Bryan Kelly" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
find last row value in column when using MATCH to find column | Excel Worksheet Functions | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
To find Multiple values in column B for a unique value in column A | Excel Worksheet Functions |