Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clean up data
I have a file that comes in weekly that I take numerous steps to clean up the
data for my reports. I am hoping to automate some of these. Could one of you experts help me to create a macro to do the following: Every third column (beginning with C thru X) contains SUMIF formulas to calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1," ",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)). If the formula results in a blank, then I need to delete that cell and the two cells to the right of it, and move the rest of the cells up. For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6, and C6 to be deleted and all existing cells in those columns to move up. This needs to be done for all the blank cells in the totals columns stated above. I have some experience with macros and VBA, but I need some help getting started on this one.... Thanks, Diane |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clean up data
Diane
Don't you mean "two cells to the LEFT"? Your explanation says to the right but your example shows to the left. The macro would look like this: Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c End Sub Please post back if you need more. HTH Otto "diaare" wrote in message ... I have a file that comes in weekly that I take numerous steps to clean up the data for my reports. I am hoping to automate some of these. Could one of you experts help me to create a macro to do the following: Every third column (beginning with C thru X) contains SUMIF formulas to calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1," ",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)). If the formula results in a blank, then I need to delete that cell and the two cells to the right of it, and move the rest of the cells up. For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6, and C6 to be deleted and all existing cells in those columns to move up. This needs to be done for all the blank cells in the totals columns stated above. I have some experience with macros and VBA, but I need some help getting started on this one.... Thanks, Diane |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clean up data
Thnaks for the response. You were correct in assuming I meant Left of the
cell. My fingers don't always type what I mean. I pasted your macro in and recieved an error on Shift:=xlUp I changed it to Shift = xlUp and it runs now. And it does exacly what I asked for, unfortunately I didn't think through my question entirely... Here is the problem, I need to add another condition to this rule if possible. (I didn't think about it until I saw it run). If there is a value in the cell 2 cells to the left of the blank, I do not want it to delete the cells in that row. So, really what I want is to find where cells in the totals column that are blank, and if the cell two to the left are blank as well, then delete those two cells and the one between them. For example: A B C D E F 1 x 5 x x 3 2 x x 4 3 4 x x 4 x 6 5 x I would only want to delete A2, B2, C2 and D3, E3, F3 This is proving to be more complicated than I thought...is it still doable? Thnaks again for your help. "Otto Moehrbach" wrote: Diane Don't you mean "two cells to the LEFT"? Your explanation says to the right but your example shows to the left. The macro would look like this: Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c End Sub Please post back if you need more. HTH Otto "diaare" wrote in message ... I have a file that comes in weekly that I take numerous steps to clean up the data for my reports. I am hoping to automate some of these. Could one of you experts help me to create a macro to do the following: Every third column (beginning with C thru X) contains SUMIF formulas to calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1," ",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)). If the formula results in a blank, then I need to delete that cell and the two cells to the right of it, and move the rest of the cells up. For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6, and C6 to be deleted and all existing cells in those columns to move up. This needs to be done for all the blank cells in the totals columns stated above. I have some experience with macros and VBA, but I need some help getting started on this one.... Thanks, Diane |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clean up data
Diane
Try this. I also added some code to freeze the screen so you won't see things jumping around as cells are deleted. The jumping around (called "painting the screen") takes a lot of time so this way the code will run faster. HTH Otto Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range Application.ScreenUpdating = False For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" And TheRng(d).Offset(, -2).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c Application.ScreenUpdating = True End Sub "diaare" wrote in message ... Thnaks for the response. You were correct in assuming I meant Left of the cell. My fingers don't always type what I mean. I pasted your macro in and recieved an error on Shift:=xlUp I changed it to Shift = xlUp and it runs now. And it does exacly what I asked for, unfortunately I didn't think through my question entirely... Here is the problem, I need to add another condition to this rule if possible. (I didn't think about it until I saw it run). If there is a value in the cell 2 cells to the left of the blank, I do not want it to delete the cells in that row. So, really what I want is to find where cells in the totals column that are blank, and if the cell two to the left are blank as well, then delete those two cells and the one between them. For example: A B C D E F 1 x 5 x x 3 2 x x 4 3 4 x x 4 x 6 5 x I would only want to delete A2, B2, C2 and D3, E3, F3 This is proving to be more complicated than I thought...is it still doable? Thnaks again for your help. "Otto Moehrbach" wrote: Diane Don't you mean "two cells to the LEFT"? Your explanation says to the right but your example shows to the left. The macro would look like this: Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c End Sub Please post back if you need more. HTH Otto "diaare" wrote in message ... I have a file that comes in weekly that I take numerous steps to clean up the data for my reports. I am hoping to automate some of these. Could one of you experts help me to create a macro to do the following: Every third column (beginning with C thru X) contains SUMIF formulas to calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1," ",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)). If the formula results in a blank, then I need to delete that cell and the two cells to the right of it, and move the rest of the cells up. For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6, and C6 to be deleted and all existing cells in those columns to move up. This needs to be done for all the blank cells in the totals columns stated above. I have some experience with macros and VBA, but I need some help getting started on this one.... Thanks, Diane |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clean up data
Thanks for your response. I was happy see that the change you made was what
I was going to do, I guess know more VBA than I thought. Unfortunately, when I try to run it brings my system to a halt, until I finally have to close Excel through Task Manager. Any ideas on what I could do to speed it up? If not maybe I'll just have to continue on with the manual process... Thanks, Diane "Otto Moehrbach" wrote: Diane Try this. I also added some code to freeze the screen so you won't see things jumping around as cells are deleted. The jumping around (called "painting the screen") takes a lot of time so this way the code will run faster. HTH Otto Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range Application.ScreenUpdating = False For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" And TheRng(d).Offset(, -2).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c Application.ScreenUpdating = True End Sub "diaare" wrote in message ... Thnaks for the response. You were correct in assuming I meant Left of the cell. My fingers don't always type what I mean. I pasted your macro in and recieved an error on Shift:=xlUp I changed it to Shift = xlUp and it runs now. And it does exacly what I asked for, unfortunately I didn't think through my question entirely... Here is the problem, I need to add another condition to this rule if possible. (I didn't think about it until I saw it run). If there is a value in the cell 2 cells to the left of the blank, I do not want it to delete the cells in that row. So, really what I want is to find where cells in the totals column that are blank, and if the cell two to the left are blank as well, then delete those two cells and the one between them. For example: A B C D E F 1 x 5 x x 3 2 x x 4 3 4 x x 4 x 6 5 x I would only want to delete A2, B2, C2 and D3, E3, F3 This is proving to be more complicated than I thought...is it still doable? Thnaks again for your help. "Otto Moehrbach" wrote: Diane Don't you mean "two cells to the LEFT"? Your explanation says to the right but your example shows to the left. The macro would look like this: Sub DeleteCells() Dim c As Long Dim d As Long Dim TheRng As Range For c = 3 To 24 Step 3 Set TheRng = Range(Cells(2, c), Cells(Rows.Count, c).End(xlUp)) For d = TheRng.Count To 1 Step -1 If TheRng(d).Value = "" Then _ TheRng(d).Offset(, -2).Resize(, 3).Delete Shift:=xlUp Next d Next c End Sub Please post back if you need more. HTH Otto "diaare" wrote in message ... I have a file that comes in weekly that I take numerous steps to clean up the data for my reports. I am hoping to automate some of these. Could one of you experts help me to create a macro to do the following: Every third column (beginning with C thru X) contains SUMIF formulas to calculate totals - ie: =IF(SUMIF(Breakdown!D:D,T6,Breakdown!C:C)<1," ",SUMIF(Breakdown!D:D,T6,Breakdown!C:C)). If the formula results in a blank, then I need to delete that cell and the two cells to the right of it, and move the rest of the cells up. For example, if C3 and C6 are blank then I need Cells A3, B3, C3, A6, B6, and C6 to be deleted and all existing cells in those columns to move up. This needs to be done for all the blank cells in the totals columns stated above. I have some experience with macros and VBA, but I need some help getting started on this one.... Thanks, Diane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Clean Up | Excel Discussion (Misc queries) | |||
clean up data | Excel Discussion (Misc queries) | |||
Clean Up Data | Excel Discussion (Misc queries) | |||
HOW DO I CLEAN UNCLEAN DATA? | Excel Worksheet Functions | |||
Get out clean XML data | Excel Discussion (Misc queries) |