Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
Hello all,
I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
with worksheets("sheet1")
if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
Hello Sir Dave Peterson,
please re-paste the code... -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull "Dave Peterson" wrote: with worksheets("sheet1") if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
Huh?
Why? driller wrote: Hello Sir Dave Peterson, please re-paste the code... -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull "Dave Peterson" wrote: with worksheets("sheet1") if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
thanks Sir Dave,
i paste the code but with no luck with... Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows regards, driller -- regards, driller ***** - dive with Jonathan Seagull "Dave Peterson" wrote: Huh? Why? driller wrote: Hello Sir Dave Peterson, please re-paste the code... -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull "Dave Peterson" wrote: with worksheets("sheet1") if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
I think it's time for you to post your code and identify the lines that had
problems. Or copy and paste from that other post (or even type it manually). Remember, my suggested code was just checking to see if there was anything on that worksheet--nothing more. driller wrote: thanks Sir Dave, i paste the code but with no luck with... Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows regards, driller -- regards, driller ***** - dive with Jonathan Seagull "Dave Peterson" wrote: Huh? Why? driller wrote: Hello Sir Dave Peterson, please re-paste the code... -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull "Dave Peterson" wrote: with worksheets("sheet1") if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
a macro...for a virgin sheet
Thank you Sir Dave,
Definitely i need more tests... your kind effort i appreciate... *i will repost for the other part...* -- regards, driller ***** - dive with Jonathan Seagull "Dave Peterson" wrote: I think it's time for you to post your code and identify the lines that had problems. Or copy and paste from that other post (or even type it manually). Remember, my suggested code was just checking to see if there was anything on that worksheet--nothing more. driller wrote: thanks Sir Dave, i paste the code but with no luck with... Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows regards, driller -- regards, driller ***** - dive with Jonathan Seagull "Dave Peterson" wrote: Huh? Why? driller wrote: Hello Sir Dave Peterson, please re-paste the code... -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull "Dave Peterson" wrote: with worksheets("sheet1") if .usedrange.address(0,0) = "A1" _ and isempty(.range("a1").value) then 'yep, sheet1 doesn't have any values/formulas in it 'do the work else msgbox "Clean up Sheet1" exit sub end if end with If you just want to start with a new worksheet, you can use: worksheets("sheet1").cells.clear 'formatting and contents or you could even delete the sheet and re-add it. On error resume next application.displayalerts = false worksheets("sheet1").delete application.displayalerts = true On error goto 0 Worksheets.Add.Name = "Sheet1" driller wrote: Hello all, I have one workbook with only 2 sheets Sheet1 <empty*virgin* Sheet2 <i have data as follows A1:A7 = 100,200,300,400,500,600,700 the above represents the rows i want to select on Sheet1 based on the above data, i want to filter the Rows on Sheet1...because i will paste array formulas on the selected rows The number of rows on sheet2 and the filtered Row(#'s) on sheet1 may vary... I want to run the code only and only if the sheet1 is *virgin*. Otherwise "Abort it!"... i hope i can just record it myself! thanks and really appreciate your guidance... regards, driller -- ***** birds of the same feather flock together..birdwise, it''s more worth knowing the edges rather than focusing in one line! Know the limits and remember the extents - dive with Jonathan Seagull -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GET PAID FOR INCOMMING!!!!-FREE VIRGIN CONNECTION!!! | Excel Worksheet Functions | |||
GET PAID FOR INCOMMING!!!!-FREE VIRGIN CONNECTION!!! | Excel Discussion (Misc queries) | |||
I am a virgin to Excel | New Users to Excel | |||
Q: Creating a macro to sort and group columns in a sheet according to another sheet | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |