Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Programming Question | Excel Discussion (Misc queries) | |||
Userform - VBA Programming Question | Excel Programming | |||
Simple programming question | Excel Programming | |||
Help with programming question | Excel Programming | |||
Cell value programming question | Excel Programming |