ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Select until blank row (https://www.excelbanter.com/excel-discussion-misc-queries/195435-select-until-blank-row.html)

Bonnie
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.

Otto Moehrbach[_2_]
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.



Bonnie
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.




Bonnie
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.




Otto Moehrbach[_2_]
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.

Bonnie
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.

Bonnie
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.

Otto Moehrbach[_2_]
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.

Otto Moehrbach[_2_]
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.


All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com