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

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

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

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

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

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