Scrolling
 

Why cant you use the mouse scroll button within the code?! That is
really annoying and I have always wondered if there was a reason to it!

On another side, what is a good snippet for selecting only filled cells
so that you dont print blank pages? Ive tried the below but its not
working, it only selects the first two lines of anything

Set rng = Range(Range("A1"), Range("I1").End(xlDown).Offset(1, 0))
rng.Select
Selection.PrintOut Copies:=1, Collate:=True

Any ideas?

Scrolling
 

lrow = Range("A65536").End(xlUp).Row
Set rng = Range("A1:I" & lrow)

The above placed into the immediate window, then enter:
? rng.address {Enter}
produces (below)

$A$1:$I$5

HTH

"Duncan" wrote in message
oups.com...
Why cant you use the mouse scroll button within the code?! That is
really annoying and I have always wondered if there was a reason to it!

On another side, what is a good snippet for selecting only filled cells
so that you dont print blank pages? Ive tried the below but its not
working, it only selects the first two lines of anything

Set rng = Range(Range("A1"), Range("I1").End(xlDown).Offset(1, 0))
rng.Select
Selection.PrintOut Copies:=1, Collate:=True

Any ideas?

Scrolling
 

I guess it depends on what is in Column I.

from the immediate window:

? selection.Address
$A$1:$I$11

I assume you are talking about scrolling with the scroll wheel in the VBE.

Do a google groups search on the VBE and Mouse and ScrollWheel
http://groups.google.com
select Advanced.

--
Regards,
Tom Ogilvy



"Duncan" wrote:

Why cant you use the mouse scroll button within the code?! That is
really annoying and I have always wondered if there was a reason to it!

On another side, what is a good snippet for selecting only filled cells
so that you dont print blank pages? Ive tried the below but its not
working, it only selects the first two lines of anything

Set rng = Range(Range("A1"), Range("I1").End(xlDown).Offset(1, 0))
rng.Select
Selection.PrintOut Copies:=1, Collate:=True

Any ideas?

Scrolling
 

Tom,

I might be being really stupid but what do you mean the immediate
window?,, im trying to put this into the code behind by find button but
I dont know what the immediate window is! ..........god I am so thick
it hurts sometimes!!


Duncan

Scrolling
 

ok,

I found out where the immediate window is, but I dont know what it is
used for and how I code it so it prints out that range.... i tried
leaving that code in the immediate window and then putting rng.printout
and various other combinations like Print rng.Address but I keep
getting "variable not defined".

There must be a dead simple way?..........

Scrolling
 

I used the immediate window for demonstration. Go to the immediate window
and do
? selection.Address(0,0,xlA1,True)

and it should tell you what range is selected on the active sheet (of course
if you don't have a range selected, it will raise an error).

The immediate window is just for debugging and checking out commands - any
command you enter there and hit enter in that line, will be immediately
executed. For example, if you put in the immediate window
Range("A:F").EntireColumn.Hidden = True <cr

then went back to the sheet, you would see columns A:F on the activesheet
are now hidden.

so if you wanted to check your code you could do in the immediate window

Range("A1",Range("I1").End(xldown)).select <cr
then see what is selected. Doing an xldown is the same and manually going
to I1, then hitting the end button, then the down arrow. Where it stops
depends on what is in the column. Jim May surmised that you have a blank
cell in your data and suggested coming up from the bottom of the sheet in A1
and then expanding out to column I. This is probably pretty good advice.
If I actually has the bottommost cells, but contains blanks, you could do

Range("A1",cells(rows.count,"I").End(xlup)).Select <cr
in the immediate window to see if you get a better result.

--
Regards,
Tom Ogilvy




"Duncan" wrote:

ok,

I found out where the immediate window is, but I dont know what it is
used for and how I code it so it prints out that range.... i tried
leaving that code in the immediate window and then putting rng.printout
and various other combinations like Print rng.Address but I keep
getting "variable not defined".

There must be a dead simple way?..........

Scrolling
 

Tom,

My code is now

Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)
rng.Select

Thank you for the info about the immediate window, Everything I do I
have to learn so this will make it a lot easer having a test pad, I
cant work out why it works now and why it didnt work before but ill
keep testing it as at the moment it works perfectly, I'll post my sub
in full below for the benefit of others reading this.

Many thanks again,

Duncan

Private Sub Report_Click()
Sheets("sheet1").Select
Dim rng As Range
Dim LroW As Range



Set rng = Range("A1:I" & Range("a65536").End(xlUp).Row)



If regTrail <= "" Then
MsgBox "must input a registration number!"
Exit Sub
End If

If Not IsNumeric(regTrail.Value) Then
MsgBox "Registration number must be a Numerical Value, Please retry"
Exit Sub
End If

Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=regTrail.Value
UserForm1.Hide

Select Case MsgBox("Print?", vbYesNo)

Case vbYes
Sheet1.Activate
rng.Select
Selection.PrintOut Copies:=1, Collate:=True

Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus
Exit Sub

Case vbNo
Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus

Exit Sub
End Select
Sheets("sheet1").Select

End Sub