| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Select until blank row
Looking for some advice for the best way to do this. Here's what I did but I
think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
I think that maybe I know what you want, so maybe something like this?
Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Maybe I didn't need to trim it - I just wanted to stop selected at the first
blank row. Thank you Otto - I'll give it a try. Bonnie "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
The only problem is this selects to the bottom of the spreadsheet and I only
want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Bonnie
How about this: HTH Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... The only problem is this selects to the bottom of the spreadsheet and I only want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Thank you Otto, I'll give it a try in the morning and let you know.
Bon "Otto Moehrbach" wrote: Bonnie How about this: HTH Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... The only problem is this selects to the bottom of the spreadsheet and I only want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Hi Otto,
I'm getting an error: Method "Range' of object '_Global' failed Thanks, Bonnie "Otto Moehrbach" wrote: Bonnie How about this: HTH Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... The only problem is this selects to the bottom of the spreadsheet and I only want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Bonnie
When you get the error, the error box has a "Debug" button on it. Click on that button and Excel will take you to the code and will highlight the offending line of code. Send that. Otto "Bonnie" wrote in message ... Hi Otto, I'm getting an error: Method "Range' of object '_Global' failed Thanks, Bonnie "Otto Moehrbach" wrote: Bonnie How about this: HTH Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... The only problem is this selects to the bottom of the spreadsheet and I only want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Select until blank row
Bonnie
Or maybe you're talking about this code that has only one line between Sub and End Sub. Note that there is only one line. That line starts with "Range" and ends with "Select". If that line is broken up into more than one line, VBA will balk. Sometimes (many times) posting code in these newsgroups will force a line wrap and that line wrap will result in an error. Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... Hi Otto, I'm getting an error: Method "Range' of object '_Global' failed Thanks, Bonnie "Otto Moehrbach" wrote: Bonnie How about this: HTH Otto Sub Maybe() Range("A1", Range("A1").End(xlDown).Offset(1)).Select End Sub "Bonnie" wrote in message ... The only problem is this selects to the bottom of the spreadsheet and I only want it selected to the first blank row. There are blank rows between each set of data and the number of rows varies but the blank tells me it's a new record. Thanks Otto in advance. "Otto Moehrbach" wrote: I think that maybe I know what you want, so maybe something like this? Sub Maybe() Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select End Sub If not, post back with a bit more info about what you are trying to do. For instance, why all the "Trim"? HTH Otto "Bonnie" wrote in message ... Looking for some advice for the best way to do this. Here's what I did but I think is cluncky: Windows("MYFILE.xls").Activate If Trim(Range("MASTER!A4")) = "" Then Range("A1:A4").Select Else If Trim(Range("Master!A5")) = "" Then Range("A1:A5").Select Else If Trim(Range("Master!A6")) = "" Then Range("A1:A6").Select Else If Trim(Range("Master!A7")) = "" Then Range("A1:A7").Select Else If Trim(Range("Master!A8")) = "" Then Range("A1:A8").Select Else If Trim(Range("Master!A9")) = "" Then Range("A1:A9").Select Else If Trim(Range("Master!A10")) = "" Then Range("A1:A10").Select End If End If End If End If End If End If End If I want to select all the rows with the last one being the blank row. Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How To Select Blank Cells within a worksheet | Excel Discussion (Misc queries) | |||
| Select NON blank cells | New Users to Excel | |||
| How do I select & use contents of first non-blank cell in row? | Excel Worksheet Functions | |||
| select and delete all blank rows | Excel Discussion (Misc queries) | |||
| I want to select the first blank cell in column A | Excel Worksheet Functions | |||
Linear Mode
