Programming Question
 

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
For Each c In r
If c.Offset(0, 3) = "no" Then
Lnam = c.Value
Fnam = c.Offset(0, 1).Value
addr = c.Offset(0, 2).Value
MsgBox ("Found " & Fnam & " " & Lnam & " @" & addr)
End If
Next c
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Jeff, Could this code be used to select and set a print area for a worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets to a
bottom row and the data stops, then set the print area and print the results
of that document. The document I'm using has 208 rows but very few times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Lastrow is the lastrow with data, so if you set your printarea to include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
Jeff, Could this code be used to select and set a print area for a
worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets to a
bottom row and the data stops, then set the print area and print the
results
of that document. The document I'm using has 208 rows but very few times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

I'm not as smart as I thought. I substituted the "D" in the macro to read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select what I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea to include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
Jeff, Could this code be used to select and set a print area for a
worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets to a
bottom row and the data stops, then set the print area and print the
results
of that document. The document I'm using has 208 rows but very few times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those
rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
I'm not as smart as I thought. I substituted the "D" in the macro to read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select what I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
Jeff, Could this code be used to select and set a print area for a
worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets to
a
bottom row and the data stops, then set the print area and print the
results
of that document. The document I'm using has 208 rows but very few
times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those
rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Thanks Tom, that took care of a small part of it but it won't print more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr: 12345
Employer: Enter Employer's Name Here Social Security Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is shown below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight Time Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25 $568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65 $113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only get the top
row that is greyed out. There is an additional column of O also that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
I'm not as smart as I thought. I substituted the "D" in the macro to
read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select what I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
Jeff, Could this code be used to select and set a print area for a
worksheet
where I need to copy and paste all the cells with active data in it.
I was looking for something to read various rows and when it gets
to
a
bottom row and the data stops, then set the print area and print the
results
of that document. The document I'm using has 208 rows but very few
times
will they all be filled. usually they only have about 20 or 30 rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of those
rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

It appears my paste didn't stay formatted but can provide the ws if need to
be attached.
BOB

"Bob Reynolds" wrote in message
...
Thanks Tom, that took care of a small part of it but it won't print more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr: 12345
Employer: Enter Employer's Name Here Social Security Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is shown below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight Time
Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25
$568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65
$113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only get the
top
row that is greyed out. There is an additional column of O also that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
I'm not as smart as I thought. I substituted the "D" in the macro to
read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select what
I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
Jeff, Could this code be used to select and set a print area for a
worksheet
where I need to copy and paste all the cells with active data in
it.
I was looking for something to read various rows and when it gets
to
a
bottom row and the data stops, then set the print area and print
the
results
of that document. The document I'm using has 208 rows but very few
times
will they all be filled. usually they only have about 20 or 30
rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of
those
rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Tom Thanks for your help.
I have one other worksheet similiar to this one, but all the cells are
filled in by being linked to another data entry worksheet, so each cell
contains a formula. It appears this see a formula as a not empty cell and
would continue to show the row as being completed.

Is there a way to make this formula distinguish from a formula returning a
blank or empty cell (the formula is still in it) and the result of the
formula?
Hope you have a positive answer for this one...
Bob


"Tom Ogilvy" wrote in message
...
This isn't a binaries news group, so attaching the file would be a
non-starter.

If the last row to be printed has a value in column A, then you should not
have any trouble.

You can send me a sample workbook if you wish and I will take a look, but
I
can't tell anything from your email.



--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
It appears my paste didn't stay formatted but can provide the ws if need
to
be attached.
BOB

"Bob Reynolds" wrote in message
...
Thanks Tom, that took care of a small part of it but it won't print
more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr: 12345
Employer: Enter Employer's Name Here Social Security Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here
Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is shown
below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight Time
Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE
INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25
$568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65
$113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only get
the
top
row that is greyed out. There is an additional column of O also that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
I'm not as smart as I thought. I substituted the "D" in the macro
to
read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to select
what
I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea
to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
Jeff, Could this code be used to select and set a print area
for
a
worksheet
where I need to copy and paste all the cells with active data
in
it.
I was looking for something to read various rows and when it
gets
to
a
bottom row and the data stops, then set the print area and
print
the
results
of that document. The document I'm using has 208 rows but very
few
times
will they all be filled. usually they only have about 20 or 30
rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to
do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple St.
Yes

What I would like to know how do (in VB) is the following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each of
those
rows

Can someone provide an example of how to accomplish this ?

Many Thanks In Advance

Programming Question
 

Sub findlastrow()
Dim lastrow As Long
Dim r As Range
LastRow = 1
for each cell in Range(Cells(1,"C"),Cells(rows.count,"C").End(xlup) )
if trim(cell.text) < "" then
lastrow = cell.row
end if
Next
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

Change "C" to the column you want to use to determine the last row.

--
Regards,
Tom Ogilvy


"Bob Reynolds" wrote in message
...
Tom Thanks for your help.
I have one other worksheet similiar to this one, but all the cells are
filled in by being linked to another data entry worksheet, so each cell
contains a formula. It appears this see a formula as a not empty cell and
would continue to show the row as being completed.

Is there a way to make this formula distinguish from a formula returning a
blank or empty cell (the formula is still in it) and the result of the
formula?
Hope you have a positive answer for this one...
Bob


"Tom Ogilvy" wrote in message
...
This isn't a binaries news group, so attaching the file would be a
non-starter.

If the last row to be printed has a value in column A, then you should
not
have any trouble.

You can send me a sample workbook if you wish and I will take a look,
but
I
can't tell anything from your email.



--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
It appears my paste didn't stay formatted but can provide the ws if
need
to
be attached.
BOB

"Bob Reynolds" wrote in message
...
Thanks Tom, that took care of a small part of it but it won't print
more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr:
12345
Employer: Enter Employer's Name Here Social Security Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here
Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is shown
below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight
Time
Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE
INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25
$568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65
$113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only get
the
top
row that is greyed out. There is an additional column of O also that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
I'm not as smart as I thought. I substituted the "D" in the
macro
to
read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to
select
what
I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your printarea
to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
Jeff, Could this code be used to select and set a print area
for
a
worksheet
where I need to copy and paste all the cells with active
data
in
it.
I was looking for something to read various rows and when
it
gets
to
a
bottom row and the data stops, then set the print area and
print
the
results
of that document. The document I'm using has 208 rows but
very
few
times
will they all be filled. usually they only have about 20 or
30
rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking to
do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main St
No
Able Mark Acort St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple
St.
Yes

What I would like to know how do (in VB) is the
following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for each
of
those
rows

Can someone provide an example of how to accomplish this
?

Many Thanks In Advance

Programming Question
 

Thanks so much, worked like a champ. I assume the LastRow=1 tells it that if
it doesn't include a product then don't read it and skip it, is that
correct?

I greatly appreciate the assistance.
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
LastRow = 1
for each cell in Range(Cells(1,"C"),Cells(rows.count,"C").End(xlup) )
if trim(cell.text) < "" then
lastrow = cell.row
end if
Next
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

Change "C" to the column you want to use to determine the last row.

--
Regards,
Tom Ogilvy


"Bob Reynolds" wrote in message
...
Tom Thanks for your help.
I have one other worksheet similiar to this one, but all the cells are
filled in by being linked to another data entry worksheet, so each cell
contains a formula. It appears this see a formula as a not empty cell
and
would continue to show the row as being completed.

Is there a way to make this formula distinguish from a formula returning
a
blank or empty cell (the formula is still in it) and the result of the
formula?
Hope you have a positive answer for this one...
Bob


"Tom Ogilvy" wrote in message
...
This isn't a binaries news group, so attaching the file would be a
non-starter.

If the last row to be printed has a value in column A, then you should
not
have any trouble.

You can send me a sample workbook if you wish and I will take a look,
but
I
can't tell anything from your email.



--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in message
...
It appears my paste didn't stay formatted but can provide the ws if
need
to
be attached.
BOB

"Bob Reynolds" wrote in
message
...
Thanks Tom, that took care of a small part of it but it won't
print
more
than the top rows.
WAGE COMPUTATION SHEET Date: 05-20-04 Identification Nr:
12345
Employer: Enter Employer's Name Here Social Security
Number:
CONFIDENTIAL
Employee (Name/Address): Enter employee's Name Here
Occupation:
This is the Occupation Section The Total Amounts Due This
1234 Any place Telephone: (876) 543-2345 Employee is
shown
below:
Raleigh, NC 283565 $1,670.01 $48.85 $1,718.86
W/W END PAY DAY HOURS WORK Salary Hourly Rate Straight
Time
Wages
Due OT Hours OT Rate OT Wages Due Wages Paid TOTAL WAGES DUE
INTEREST
(Coeff.) TOTAL AMOUNT DUE
01-01-04 55.00 $500.00 $9.09 $500.00 15.00 $4.55 $68.25
$568.25
$17.50 $585.75
0.0308
01-01-04 55.00 $100.00 $1.82 $100.00 15.00 $0.91 $13.65
$113.65
$3.50 $117.15
0.0308

This is what I'm trying to work with, but with 208 rows. I only
get
the
top
row that is greyed out. There is an additional column of O also
that
contains a vlookup formula. (All numbers are ficticous)

I can provide the worksheet if necessary.
Thanks
BOB

"Tom Ogilvy" wrote in message
...
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)
r.printout
' or
'Activesheet.PageSetup.PrintArea = r.address(external:=true)
'Activesheet.Printout
End Sub

-
Regards,
Tom Ogilvy

"Bob Reynolds" wrote in
message
...
I'm not as smart as I thought. I substituted the "D" in the
macro
to
read
"N" because my spreadsheet goes all the way over to N.
Sub findlastrow()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:N" & lastrow)

End Sub

So how do I make this macro here work on my spreadsheet to
select
what
I
want and then set the print area??
and then print? I hope that isn't too much to ask
Thanks

BOB

"Tom Ogilvy" wrote in message
...
Lastrow is the lastrow with data, so if you set your
printarea
to
include
just down to that row it should do what you want.

--
Regards,
Tom Ogilvy

"Bob Reynolds" wrote
in
message
...
Jeff, Could this code be used to select and set a print
area
for
a
worksheet
where I need to copy and paste all the cells with active
data
in
it.
I was looking for something to read various rows and when
it
gets
to
a
bottom row and the data stops, then set the print area and
print
the
results
of that document. The document I'm using has 208 rows but
very
few
times
will they all be filled. usually they only have about 20
or
30
rows
completed and I don't want to print all the empty rows.

Can you give me an answer or an idea for what I'm looking
to
do?
Thanks
Bob Reynolds

"Jeff" wrote in message
...
John,

Try this macro code for starters:

Private Sub dofindnos()
Dim lastrow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Row
Set r = ActiveSheet.Range("A1:D" & lastrow)
End Sub

Jeff
-----------------


"Jon Turner" wrote:

I'm new to Excel programming, so please be kind if
this
sounds like an elementary question.

I have a Spread Sheet with the Following Columns/Rows

LastName FirstName Address
Submitted
Smith John Main
St
No
Able Mark Acort
St
Yes
Marks Dave Bell Rd.
No
Cane Jack Maple
St.
Yes

What I would like to know how do (in VB) is the
following:
1. Retreive all Rows that have a submitted value = No
2. Using the column names retrieve the values for
each
of
those
rows

Can someone provide an example of how to accomplish
this
?

Many Thanks In Advance