PrintTitleRows 2

[SteveDB1]

Ok, I've got the intitial elements working.

Do Until Selection.Interior.ColorIndex <> -4142
ActiveCell.Offset(1, 0).Select

Loop

If Selection.Interior.ColorIndex <> -4142 Then

'how do I define the last row selected?
'I tried the following and get a 1004 error.

range("$1: ActiveCell.EntireRow.cells()").Select
' I've also tried: range("$1:entirerow.cells()").select
' and range("$1:entirerow").select
' none of those work, and all throw a 1004 error.

End If

My goal now is to place the last row selected in the do loop into a
range().select (or a better choice) function so I can define the
PrintTitleRows element of PageSetUp

With ActiveSheet.PageSetUp
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

comnponent.

How would I accomplish this?

Thank you.

 

[Jim Cone]

Sub WhichOne()
'Returns the row number of the last row in selection.
Dim lngRw As Long
lngRw = Selection.Rows(Selection.Rows.Count).Row
MsgBox lngRw
End Sub
'--
'If the Selection is Range(B5:C10)then:
'Selection.Rows.Count ...returns the number 6.
'Selection.Rows(6) ...returns the last row in the selection (range object-cells B10:C10)
'Selection.Rows(6).Row ...returns the actual Excel row number or 10

'However, if you are just using the selection to define the Rows to Repeat at Top then
' ActiveSheet.PrintTitleRows = Selection.EntireRow.Address
--
Jim Cone
Portland, Oregon USA



"SteveDB1" <[email protected]>
wrote in message
Ok, I've got the intitial elements working.

Do Until Selection.Interior.ColorIndex <> -4142
ActiveCell.Offset(1, 0).Select
Loop

If Selection.Interior.ColorIndex <> -4142 Then

'how do I define the last row selected?
'I tried the following and get a 1004 error.

range("$1: ActiveCell.EntireRow.cells()").Select
' I've also tried: range("$1:entirerow.cells()").select
' and range("$1:entirerow").select
' none of those work, and all throw a 1004 error.

End If

My goal now is to place the last row selected in the do loop into a
range().select (or a better choice) function so I can define the
PrintTitleRows element of PageSetUp

With ActiveSheet.PageSetUp
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

comnponent.

How would I accomplish this?

Thank you.

 

[SteveDB1]

Good morning Jim.
Thank you for the response yesterday evening.

At this point, I've got a do until loop that iterates through all of the
first few rows that are not filled with an interior color. When it finds a
row that is ...interior.colorindex <>-4142 it stops.
My next goal is to select the range from the start row, to the last row in
my loop-- the i-th row--so that I can perform the task of selecting those
rows, then fulfill a subsequent task.

It's the selection of those rows that's stumping me.

Yesterday afternoon after I'd posted this, I finally found the code to make
a selection, but it either selected the i-th row, downward to the 17th row
below the i-th row, or skipped some rows and then selected i- number of rows.
I know that it's something simple, but so far I haven't found it.

My existing code so far is:
----------------------------------
Dim i As Integer
i = 1
Do Until Selection.Interior.ColorIndex <> -4142
ActiveCell.Offset(1, 0).Select
i = 1 + i
Loop

If Selection.Interior.ColorIndex <> -4142 Then
'everything works until here.
'this next function does not work.
ActiveSheet.Select 'I chose this because the help file said I had to.
Selection.range("a1", Row.cell(i)).Select

End If
---------------------------------

for whatever reason, instead of selecting cell a1, it treats the i-th row as
a1, and then dropped down 17 more rows, and selected all of them.
I specifically am seeking to select row 1, and then include row 1 to the
i-th row.

I hope that makes my thinking clearer. If not, please let me know, and I'll
try further to clarify.
Again, I really appreciate your time and assistance.

 

[Jim Cone]

I think this is what you want?...
'--
Sub TopStuff()
' always use a Long for a row number
Dim i As Long
Dim rngTop As Range
i = 1
' specify the starting point.
Range("A1").Select

Do Until Selection.Interior.ColorIndex <> -4142
ActiveCell.Offset(1, 0).Select
i = 1 + i
Loop
Set rngTop = Range("A1", Cells(i, 1))
MsgBox rngTop.Address
End Sub
'--
'In general, selecting cells is not good practice.
'However, in place of the last two lines you could use...
Range("A1", Cells(i, 1)).Select
MsgBox Selection.Address
--
Also, there is more than one way to code the above.
If the first colored cell was at row 25000,
then code execution might take longer than you want.
'--
'Further, you can only make a selection on the active sheet.
'That is why you should select a sheet first,then make a selection.
'In your case everything takes place on the active sheet, so
'no sheet selection is necessary.
'--
Jim Cone
Portland, Oregon USA



"SteveDB1"
<[email protected]>
wrote in message
Good morning Jim.
Thank you for the response yesterday evening.

At this point, I've got a do until loop that iterates through all of the
first few rows that are not filled with an interior color. When it finds a
row that is ...interior.colorindex <>-4142 it stops.
My next goal is to select the range from the start row, to the last row in
my loop-- the i-th row--so that I can perform the task of selecting those
rows, then fulfill a subsequent task.

It's the selection of those rows that's stumping me.
Yesterday afternoon after I'd posted this, I finally found the code to make
a selection, but it either selected the i-th row, downward to the 17th row
below the i-th row, or skipped some rows and then selected i- number of rows.
I know that it's something simple, but so far I haven't found it.
My existing code so far is:
----------------------------------
Dim i As Integer
i = 1
Do Until Selection.Interior.ColorIndex <> -4142
ActiveCell.Offset(1, 0).Select
i = 1 + i
Loop

If Selection.Interior.ColorIndex <> -4142 Then
'everything works until here.
'this next function does not work.
ActiveSheet.Select 'I chose this because the help file said I had to.
Selection.range("a1", Row.cell(i)).Select

End If
---------------------------------

for whatever reason, instead of selecting cell a1, it treats the i-th row as
a1, and then dropped down 17 more rows, and selected all of them.
I specifically am seeking to select row 1, and then include row 1 to the
i-th row.
I hope that makes my thinking clearer. If not, please let me know, and I'll
try further to clarify.
Again, I really appreciate your time and assistance.

 

[SteveDB1]

A second followup.....

I found something that worked-- as a stand alone, with the code I'd shown in
my first followup above.

range("1:1", Selection.End(xlUp).Offset(1, 0)).EntireRow.Select

Once I got that to work-- in my general testing macro on all worksheets of
importance, I placed it in my pagesetup macro for a preliminary test run.


I got back an error stating that it's unable to set the PrintTitleRows
property of the PageSetUp Class.
I then tried setting my function that did work to a variable name, and it
then threw another error stating the object or with block was not set (the
original issue that was stumping me earlier).

So, I guess my next question would be-- can I set .PrintTitleRows to a
variable name? or does it require a constant row value such as "$1:$5"

I would've thought that even if the range was a function like:

range("1:1", Selection.End(xlUp).Offset(1, 0)).EntireRow.Select

it would've worked.

Any ideas at this point would be appreciated.

 

[SteveDB1]

It actually works-- of course it does, you did it <vvvvbg>.
However, as with my immediately previous post, I've found that I'm unable to
set the PrintTitleRows using a range function, or a variable.

I agree with the 25000 rows. Thankfully, in all of my cases encountered so
far (which is why I chose this way-- if it was in the few to several dozens
even, I'd be choosing another way), the farthest down the first row that's
colored is row 18. And I think that's only one or two files-- out of the 700
to 800 we have, I've done some 40 to 60% of those. Most files range from 5 to
14 rows.

Thus, I'm now back to-- can I set my PrintTitleRows by use of anything other
than a constant? I.e., .PrintTitleRows = "$1:$12"

because it's thrice rejected

ActiveSheet.PageSetUp.PrintTitleRows = Range("A1", Cells(i, 1))
and

Set rngTop = Range("A1", Cells(i, 1))

ActiveSheet.PageSetUp.PrintTitleRows = rngTop

 

[Jim Cone]

PrintTitleRows requires a String...
The Address property of a range object returns a String.

Rows(6).Address
-or-
Selection.Address

The String provided should Not include workbook or worksheet references.
--
Jim Cone
Portland, Oregon USA


"SteveDB1" <[email protected]>
wrote in message
A second followup.....
I found something that worked-- as a stand alone, with the code I'd shown in
my first followup above.

range("1:1", Selection.End(xlUp).Offset(1, 0)).EntireRow.Select

Once I got that to work-- in my general testing macro on all worksheets of
importance, I placed it in my pagesetup macro for a preliminary test run.


I got back an error stating that it's unable to set the PrintTitleRows
property of the PageSetUp Class.
I then tried setting my function that did work to a variable name, and it
then threw another error stating the object or with block was not set (the
original issue that was stumping me earlier).

So, I guess my next question would be-- can I set .PrintTitleRows to a
variable name? or does it require a constant row value such as "$1:$5"

I would've thought that even if the range was a function like:

range("1:1", Selection.End(xlUp).Offset(1, 0)).EntireRow.Select

it would've worked.

Any ideas at this point would be appreciated.

 

[SteveDB1]

hi again.
I found the Print Title Rows in the help file.
It states that A-1 notation is required for setting the range to be used in
this.

Yet, in the sample used, they do what I thought I was trying to do.

ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(3).Address

I guess at this point, I'm stumped again.

I just tried a variation of the activesheet.rows().address as
range("1:1", cells(1)).address thinking that the .address was the key. It
failed.
I then tried
activesheet.rows("1:1", i).address, and that failed as well.
I then tried
activesheet.rows(i).address and it selected the i-th row.

 

[SteveDB1]

Well, I found that I had to modify the function given in the help file.
from

ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(3).Address

to

ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.range("1:1",
Rows(i)).Address

I just got done testing it on 4 different sheets, about 10 times and it
worked on each one with no quibbles.
I think we have a winner.

Jim, thank you for all your help-- even if you don't think you did as much
as I've made it sound like-- you still read my frustrations/dribble, and that
helped immensely.

 

[Jim Cone]

To use the Range Object Address property you must have a valid range object.
range("1:1", cells(1)) and activesheet.rows("1:1", i) are not valid callouts.
While activesheet.rows(i) is legitimate. So this...
'--
Dim i As Long
i = 6
ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(i).Address
'--
'will add "$6:$6" to the RowsToRepeatAtTop field in the pagesetup dialog

Note: RowsToRepeatAtTop uses entire rows.
You cannot have part of a row repeating at top.
Excel is forgiving in that it enters the entire row even when you only specify a few cells.

However, I would use, for example, Range("A1").EntireRow instead of just Range("A1")...
ActiveSheet.PageSetup.PrintTitleRows = Activesheet.Range("A1").EntireRow.Address
--
Jim Cone
Portland, Oregon USA


"SteveDB1" <[email protected]>
wrote in message
hi again.
I found the Print Title Rows in the help file.
It states that A-1 notation is required for setting the range to be used in
this.

Yet, in the sample used, they do what I thought I was trying to do.

ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(3).Address

I guess at this point, I'm stumped again.

I just tried a variation of the activesheet.rows().address as
range("1:1", cells(1)).address thinking that the .address was the key. It
failed.
I then tried
activesheet.rows("1:1", i).address, and that failed as well.
I then tried
activesheet.rows(i).address and it selected the i-th row.