Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
OK, I am going to try to explain this as well as I can. I have a sheet that
has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell¦ It is a lot of info¦ My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have 0s in them since all of the names get pushed left. Is there a way that I can say something along the lines of Find the first cell from A5 AG5 that contains a 0 and then Delete the range [said column containing 0]1 AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 AG31) of cells that do not actually have any real data in them. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
Sub ABC()
Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
OK that makes so much sence not that I see it... Only one more thing.. What
do I need to chang .ClearContents to to make it actually delete the cells and shift the blank area left so that I do not even see them at all. I tried changing it to: Range(cell.Offset(-4, 0), Range("AG31")).Selection.Delete Shift:=xlToLeft and got nowhere with it... What am I missing? Thanks again.. "Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
Great code is always so simple... It makes sence now that I am looking at
it. Only one more thing. What would I have to change .ClearContents to to make it just del that entire range and shift the blank sells to the left? I changed it to: Range(cell.Offset(-4, 0), Range("AG31")).Selection.Delete Shift:=xlToLeft and go nowhere with it... What am I missing here? Thanks again.. "Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
Great code is always so simple... It makes sence now that I am looking at
it. Only one more thing. What would I have to change .ClearContents to to make it just del that entire range and shift the blank sells to the left? I changed it to: Range(cell.Offset(-4, 0), Range("AG31")).Selection.Delete Shift:=xlToLeft and go nowhere with it... What am I missing here? Thanks again.. "Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
Sub ABC()
Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")) _ .Delete Shift:=xlShifttoLeft exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK that makes so much sence not that I see it... Only one more thing.. What do I need to chang .ClearContents to to make it actually delete the cells and shift the blank area left so that I do not even see them at all. I tried changing it to: Range(cell.Offset(-4, 0), Range("AG31")).Selection.Delete Shift:=xlToLeft and got nowhere with it... What am I missing? Thanks again.. "Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete specific range of cells if containing "0"
u the man... that worked great.. ty so much.
"Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")) _ .Delete Shift:=xlShifttoLeft exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK that makes so much sence not that I see it... Only one more thing.. What do I need to chang .ClearContents to to make it actually delete the cells and shift the blank area left so that I do not even see them at all. I tried changing it to: Range(cell.Offset(-4, 0), Range("AG31")).Selection.Delete Shift:=xlToLeft and got nowhere with it... What am I missing? Thanks again.. "Tom Ogilvy" wrote: Sub ABC() Dim cell as Range for each cell in Range("A5:AG5") if cell.Value = 0 then Range(cell.offset(-4,0),Range("AG31")).ClearContents exit for end if Next End Sub -- Regards, Tom Ogilvy "ElkySS" wrote in message ... OK, I am going to try to explain this as well as I can. I have a sheet that has a lot of data that is copied out of several web pages at work and placed into Excel below row 40. All of that data gets hidden and all that is left is a report above row 40 that is pulling specific pieces of what was copied and doing some conditional formatting. In Every other cell from I5 - AG5 are names of managers at work. The cells that that skipped are hidden so that they can do some additional calculations for the conditional formatting. Then in rows 6 - 31 are the stats that are being pulled from the data I mentioned earlier. This report is set up to run for as many as 13 managers at a time, but may be ran for less if there are not as many involved in the html reports. All of that works fine from what I can tell. It is a lot of info. My question is, If I run it for say 9 managers rather than the 13 it is set up for, the last 4 columns that are showing (as well as the hidden ones) all have "0"'s in them since all of the names get pushed left. Is there a way that I can say something along the lines of 'Find the first cell from A5 - AG5 that contains a "0" and then Delete the range [said column containing "0"]1 - AG31. I can not delete the entire column because it would cause missing data from what has been copied in below and hidden. I only want to delete the range (?1 - AG31) of cells that do not actually have any real data in them. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calcuting sum of specific word "P" within specific cell range (A5: | Excel Worksheet Functions | |||
If specific # of certain characters in a range, "OK", if not "Aler | Excel Worksheet Functions | |||
Multiple "Range" with "Cells" property? | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |