Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
I need some help cleaning up a spread sheet. I have several worksheets with
data on them. I would like to delete any rows of data that don't contain one of 3 or 4 specific values in column F of that row. For example if column F of row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then delete the row. Also, some of the columns have spaces in the beginning and at the end of the actual value that I need removed at the same time. For example a field may have " Free Checking is Here " and I need all of the spaces on the end of that value removed but retaining the spaces between the words. Lastly, row 1 on all of the sheets contains a header row. Any help is GREATLY appreciated!! Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
This macro will do what you want. I assumed your data went from A2 to Hx
where "x" is any number you wish. Change this as needed to fit your data. HTH Otto Sub CleanUp() Dim RngColA As Range Dim i As Range Dim c As Long Application.ScreenUpdating = False Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For c = RngColA.Count To 1 Step -1 If RngColA(c).Offset(, 5) < "RED" And _ RngColA(c).Offset(, 5) < "WHITE" And _ RngColA(c).Offset(, 5) < "BLUE" Then _ RngColA(c).EntireRow.Delete Next c Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngColA.Resize(, 8) i.Value = Application.Trim(i.Value) Next i Application.ScreenUpdating = True End Sub "hshayh0rn" wrote in message ... I need some help cleaning up a spread sheet. I have several worksheets with data on them. I would like to delete any rows of data that don't contain one of 3 or 4 specific values in column F of that row. For example if column F of row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then delete the row. Also, some of the columns have spaces in the beginning and at the end of the actual value that I need removed at the same time. For example a field may have " Free Checking is Here " and I need all of the spaces on the end of that value removed but retaining the spaces between the words. Lastly, row 1 on all of the sheets contains a header row. Any help is GREATLY appreciated!! Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Thanks Otto but the column I need evaluated is actually E so can you tell me
which value in your code I should change? When I ran this code it deleted everything except the first row. Also, I'm sure this is a lot more complex but I was just informed that I may need to import the data several times in the next few weeks and that really takes a long time so I'm wondering if there is an easy way to code the import process. Here is the issue though. The txt file is over 500,000 line which means I can't import it to a single worksheet so I have been importing the data to a worksheet. Manually stripping out the stuff I don't need (hence the previous request) and then importing the next 65,536 records, striping out what I don't need. Doing a cut of what's left and pasting it to the original worksheet (that now has some room after I filter out what I don't need) and repeat the process until I have 65,536 records of data I need in one sheet and then I begin all over again with a new sheet until all 500,000 line of the txt file have been copied and filtered. Any way to automate this? "Otto Moehrbach" wrote: This macro will do what you want. I assumed your data went from A2 to Hx where "x" is any number you wish. Change this as needed to fit your data. HTH Otto Sub CleanUp() Dim RngColA As Range Dim i As Range Dim c As Long Application.ScreenUpdating = False Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For c = RngColA.Count To 1 Step -1 If RngColA(c).Offset(, 5) < "RED" And _ RngColA(c).Offset(, 5) < "WHITE" And _ RngColA(c).Offset(, 5) < "BLUE" Then _ RngColA(c).EntireRow.Delete Next c Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngColA.Resize(, 8) i.Value = Application.Trim(i.Value) Next i Application.ScreenUpdating = True End Sub "hshayh0rn" wrote in message ... I need some help cleaning up a spread sheet. I have several worksheets with data on them. I would like to delete any rows of data that don't contain one of 3 or 4 specific values in column F of that row. For example if column F of row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then delete the row. Also, some of the columns have spaces in the beginning and at the end of the actual value that I need removed at the same time. For example a field may have " Free Checking is Here " and I need all of the spaces on the end of that value removed but retaining the spaces between the words. Lastly, row 1 on all of the sheets contains a header row. Any help is GREATLY appreciated!! Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro help
Change the Offset(,5) to Offset(,4).
As written this macro will delete every row that doesn't have RED, WHITE, or BLUE in Column F (now E). Then it Trims (removes spaces) in every cell in the range that is left after the row deletions. I can't help you with the import task. That depends on the source of the data and I have not done importing. Otto "hshayh0rn" wrote in message ... Thanks Otto but the column I need evaluated is actually E so can you tell me which value in your code I should change? When I ran this code it deleted everything except the first row. Also, I'm sure this is a lot more complex but I was just informed that I may need to import the data several times in the next few weeks and that really takes a long time so I'm wondering if there is an easy way to code the import process. Here is the issue though. The txt file is over 500,000 line which means I can't import it to a single worksheet so I have been importing the data to a worksheet. Manually stripping out the stuff I don't need (hence the previous request) and then importing the next 65,536 records, striping out what I don't need. Doing a cut of what's left and pasting it to the original worksheet (that now has some room after I filter out what I don't need) and repeat the process until I have 65,536 records of data I need in one sheet and then I begin all over again with a new sheet until all 500,000 line of the txt file have been copied and filtered. Any way to automate this? "Otto Moehrbach" wrote: This macro will do what you want. I assumed your data went from A2 to Hx where "x" is any number you wish. Change this as needed to fit your data. HTH Otto Sub CleanUp() Dim RngColA As Range Dim i As Range Dim c As Long Application.ScreenUpdating = False Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For c = RngColA.Count To 1 Step -1 If RngColA(c).Offset(, 5) < "RED" And _ RngColA(c).Offset(, 5) < "WHITE" And _ RngColA(c).Offset(, 5) < "BLUE" Then _ RngColA(c).EntireRow.Delete Next c Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In RngColA.Resize(, 8) i.Value = Application.Trim(i.Value) Next i Application.ScreenUpdating = True End Sub "hshayh0rn" wrote in message ... I need some help cleaning up a spread sheet. I have several worksheets with data on them. I would like to delete any rows of data that don't contain one of 3 or 4 specific values in column F of that row. For example if column F of row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then delete the row. Also, some of the columns have spaces in the beginning and at the end of the actual value that I need removed at the same time. For example a field may have " Free Checking is Here " and I need all of the spaces on the end of that value removed but retaining the spaces between the words. Lastly, row 1 on all of the sheets contains a header row. Any help is GREATLY appreciated!! Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |