How to do the following selection in Visual Basic?

Search for XX.

Hold shift key.

Start selection.

Search for YY.

End selection.

Print selection.


Thanks for your help, as always,

Dave

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
HTH...

Jim Thomlinson


"David Godinger" wrote:

How to do the following selection in Visual Basic?

Search for XX.

Hold shift key.

Start selection.

Search for YY.

End selection.

Print selection.


Thanks for your help, as always,

Dave

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

I got a Visual Basic error when I tried to run this code.

Compile error: Invalid or unqualified reference

(By the way, when the code came up on screen, ".Find" was highlighted.)

- - -

Under Help, I got the following:

Invalid or unqualified reference

An identifier beginning with a period is valid only within a With
block. This error has the following cause and solution:

The identifier begins with a period.
Complete the qualification of the identifier or remove the period.



Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

I bet you changed something in Jim's suggested code that broke the procedure.

You may want to try again or post your current code.

And don't delete that "With Sheets("Sheet1").Cells" line. But do change it to
the correct worksheet name.

David Godinger wrote:

I got a Visual Basic error when I tried to run this code.

Compile error: Invalid or unqualified reference

(By the way, when the code came up on screen, ".Find" was highlighted.)

- - -

Under Help, I got the following:

Invalid or unqualified reference

An identifier beginning with a period is valid only within a With
block. This error has the following cause and solution:

The identifier begins with a period.
Complete the qualification of the identifier or remove the period.

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--

Dave Peterson

Thanks Jim and Dave for all your help! Yes, I did make two mistakes.

1) As you suspected, I deleted the "With Sheets("Sheet1").Cells" line.

2) Also, I used an incomplete string for the search, which it
apparently refused to find. For example

Set rngStart = .Find(What:="XX", _ 'is what I used,but

I should have told it to search for everything in the cell, such as

Set rngStart = .Find(What:="XXYYZZ", _ 'the complete cell content


Dave Peterson wrote:

I bet you changed something in Jim's suggested code that broke the procedure.

You may want to try again or post your current code.

And don't delete that "With Sheets("Sheet1").Cells" line. But do change it to
the correct worksheet name.

David Godinger wrote:

I got a Visual Basic error when I tried to run this code.

Compile error: Invalid or unqualified reference

(By the way, when the code came up on screen, ".Find" was highlighted.)

- - -

Under Help, I got the following:

Invalid or unqualified reference

An identifier beginning with a period is valid only within a With
block. This error has the following cause and solution:

The identifier begins with a period.
Complete the qualification of the identifier or remove the period.

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!


Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--

Dave Peterson

Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -


Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub


Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range


Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub


Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

This worked for me. I modified it slightly to show the range was selected ok.

Option Explicit
Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells

Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub

.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select '.printout
End With

End Sub

David Godinger wrote:

Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--

Dave Peterson

Searching for the entire value in the cell wouldn't be required if you
changed xlWhole to xlPart


Set rngStart = .Find(What:="XX", _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
MatchCase:=False)


--
Regards,
Tom Ogilvy


"David Godinger" wrote in message
...
Thanks Jim and Dave for all your help! Yes, I did make two mistakes.

1) As you suspected, I deleted the "With Sheets("Sheet1").Cells" line.

2) Also, I used an incomplete string for the search, which it
apparently refused to find. For example

Set rngStart = .Find(What:="XX", _ 'is what I used,but

I should have told it to search for everything in the cell, such as

Set rngStart = .Find(What:="XXYYZZ", _ 'the complete cell content


Dave Peterson wrote:

I bet you changed something in Jim's suggested code that broke the
procedure.

You may want to try again or post your current code.

And don't delete that "With Sheets("Sheet1").Cells" line. But do change
it to
the correct worksheet name.

David Godinger wrote:

I got a Visual Basic error when I tried to run this code.

Compile error: Invalid or unqualified reference

(By the way, when the code came up on screen, ".Find" was highlighted.)

- - -

Under Help, I got the following:

Invalid or unqualified reference

An identifier beginning with a period is valid only within a
With
block. This error has the following cause and solution:

The identifier begins with a period.
Complete the qualification of the identifier or remove the
period.

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and
Dr. Martin King

--
Please delete "ANTI-SPAM" from email address

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
Martin King

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="Start of things to buy", _
LookAt:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1)

Set rngEnd = .Find(What:="End of extra things to buy", _
LookAt:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
set rngEnd = rngEnd.EntireRow.Cells(1)

.Range(rngStart, rngEnd).PrintOut
End With
End Sub

not the period preceding Range and moved the End With to the bottom.

Also, if you want to print multiple columns, say out to column J as an
example

.Range(rngStart, rngEnd).Resize(,10).PrintOut


--
Regards,
Tom Ogilvy


"David Godinger" wrote in message
...
Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -


Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub


Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range


Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub


Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address

David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
Martin King

Hi Dave!

Somehow this didn't work for me, until I made the following changes:

I deleted the last two lines:
.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select 'printout

I added this one at the end, which I got from the previous versions:
Range(rngStart, rngEnd).PrintOut

QUESTION:
My VBA screen displays a line after
Option Explicit

Before the line below
Sub PrintThisRange()

Does that show that something's not working, or is it just a cosmetic
issue?

Thanks for all your help! You guys know at least ten times as much as I do
about VBA!

Best and Thanks,

Dave


Dave Peterson wrote:

This worked for me. I modified it slightly to show the range was selected ok.

Option Explicit
Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells

Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub

.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select '.printout
End With

End Sub

David Godinger wrote:

Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Hi Tom!

Thanks mucho!

This really hit the nail on the head! The last line--.Range(rngStart,
rngEnd).Resize(,10).PrintOut--which showed me how to select multiple
columns for printing, anticipated my final question!

Everything now works for me.

I can only imagine how much you guys must have studied to write this code.
I have in my possession the following three books:

Running Excel 5 for Windows, Fourth Edition
By Mark Dodge, Chris Kinata, and Craig Stinson

Excel 2003, Power Programming with VBA
By John Walkenbach

Definitive Guide to Excel VBA
By Michael Kofler

Any suggestions for me for improving? I never got a systematic knowledge of
VBA syntax. When I do one of my amateur projects, I copy and paste stuff
that already worked elsewhere and then modify it until I get my results.
I'm sure a pro would see my stuff as grossly sloppy and inefficient.

Thanks much for all your help.

Best,

Dave



"Tom Ogilvy" wrote:

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="Start of things to buy", _
LookAt:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1)

Set rngEnd = .Find(What:="End of extra things to buy", _
LookAt:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
set rngEnd = rngEnd.EntireRow.Cells(1)

.Range(rngStart, rngEnd).PrintOut
End With
End Sub

not the period preceding Range and moved the End With to the bottom.

Also, if you want to print multiple columns, say out to column J as an
example

.Range(rngStart, rngEnd).Resize(,10).PrintOut

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

I changed those two lines so that you could see what range was selected. And to
select a range, that sheet has to be selected.

And the dots (in .range()) means that it refers to the object in the previous
With statement. In this case, the List worksheet.

If you change those back, you'll be able to see what would have printed.

Column A of the cell with that first Find to the cell (no matter where it was)
in the second find.

David Godinger wrote:

Hi Dave!

Somehow this didn't work for me, until I made the following changes:

I deleted the last two lines:
.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select 'printout

I added this one at the end, which I got from the previous versions:
Range(rngStart, rngEnd).PrintOut

QUESTION:
My VBA screen displays a line after
Option Explicit

Before the line below
Sub PrintThisRange()

Does that show that something's not working, or is it just a cosmetic
issue?

Thanks for all your help! You guys know at least ten times as much as I do
about VBA!

Best and Thanks,

Dave

Dave Peterson wrote:

This worked for me. I modified it slightly to show the range was selected ok.

Option Explicit
Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells

Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub

.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select '.printout
End With

End Sub

David Godinger wrote:

Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--

Dave Peterson

Ps. The "Option Explicit" at the top of the module tells excel that you want to
be forced to declare your variables. It's a good thing to use.

And blank lines in code make for readable code--in this case, it wouldn't
matter.

David Godinger wrote:

Hi Dave!

Somehow this didn't work for me, until I made the following changes:

I deleted the last two lines:
.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select 'printout

I added this one at the end, which I got from the previous versions:
Range(rngStart, rngEnd).PrintOut

QUESTION:
My VBA screen displays a line after
Option Explicit

Before the line below
Sub PrintThisRange()

Does that show that something's not working, or is it just a cosmetic
issue?

Thanks for all your help! You guys know at least ten times as much as I do
about VBA!

Best and Thanks,

Dave

Dave Peterson wrote:

This worked for me. I modified it slightly to show the range was selected ok.

Option Explicit
Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells

Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub

.Select 'to be able to select the range below
.Range(rngStart, rngEnd).Select '.printout
End With

End Sub

David Godinger wrote:

Hi!

Code still works fine as before the recent change. However, I can't get it
to start the selection at the first column.

Here's what I have so far:
- - -

Sub PrintThisRange()

Dim rngStart As Range
Dim rngEnd As Range

With Sheets("List").Cells
On Error Resume Next
Set rngStart = .Find(What:="-------Start of things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub

Set rngStart = rngStart.EntireRow.Cells(1) 'Wonder why this line
'doesn't start a new range

Set rngEnd = .Find(What:="-------End of extra things to buy-------", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

Dave Peterson wrote:

Right after this line:
If rngStart Is Nothing Then Exit Sub
'add this line
Set rngStart = rngStart.entirerow.cells(1)

David Godinger wrote:

Now that I've seen that this code works, I'd like to include one more
command and I think I'm in business. After we find "XX", I want to go to
the first column,and start the print range from there.

Something like what this command does:

Cells(ActiveCell.Row, 1).Select

Thanks again!

Jim Thomlinson wrote:

Something like this...

Sub PrintRange()
Dim rngStart As Range
Dim rngEnd As Range

With Sheets("Sheet1").Cells 'Change this to suit
On Error Resume Next
Set rngStart = .Find(What:="XX", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngStart Is Nothing Then Exit Sub
Set rngEnd = .Find(What:="YY", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngEnd Is Nothing Then Exit Sub
End With
Range(rngStart, rngEnd).PrintOut
End Sub

--
Please delete "ANTI-SPAM" from email address
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

--

Dave Peterson