Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows based on text in cell & formatting cell based on text in column beside it
I'm okay with formulas in excel, but have never done much on macros or vb. I now have an issue where I'm pretty sure I will need a more complext macro than my skills at this point. I have a large file that is imported from an ERP type application to excel. The file is generally around 25000-35000 rows. I'm trying to get the data cleaned up to enable sorting, custom filtering and pivot table creation. There few key things I'm trying to automate
A: Deleting rows that have certain text in them. Generally, the text would be in the same column. For the most part these are subtotal type rows, but not always B. Formatting a cell based on the text value of a cell beside it. Specifically, I have cell that has the $ amount and is formatted as a number (e.g. 5416.38), but in the column beside it is a blank cell or the text "CR" (i.e. credit). What I want to do is if the cell is CR then format the number cell as a negative number (e.g. -5416.38) There are hundreds of these rows and thousands of these CR entries Thanks...steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows based on text in cell & formatting cell based on text in column beside it
Try this first
http://www.rondebruin.nl/delete.htm Post back if you need more help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Steve" wrote in message ... I'm okay with formulas in excel, but have never done much on macros or vb. I now have an issue where I'm pretty sure I will need a more complext macro than my skills at this point. I have a large file that is imported from an ERP type application to excel. The file is generally around 25000-35000 rows. I'm trying to get the data cleaned up to enable sorting, custom filtering and pivot table creation. There few key things I'm trying to automate: A: Deleting rows that have certain text in them. Generally, the text would be in the same column. For the most part these are subtotal type rows, but not always. B. Formatting a cell based on the text value of a cell beside it. Specifically, I have cell that has the $ amount and is formatted as a number (e.g. 5416.38), but in the column beside it is a blank cell or the text "CR" (i.e. credit). What I want to do is if the cell is CR then format the number cell as a negative number (e.g. -5416.38). There are hundreds of these rows and thousands of these CR entries. Thanks...steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows based on text in cell & formatting cell based on text in column beside it
I hope the following code below helps out
Sub DeletingRows( caseText As Strin Range("A1").Select 'Assumes the value used for deleting row is in column A 'change value to column where value is located Do Until x = 10 'Change number for extra number of blanks between dat Select Case (caseText Case Is = "Sub Total" 'You can change to any value you wish to delet Selection.EntireRow.Delet Case Is = "Total" 'You can add as many Case is ="TEXT" line as neede Selection.EntireRow.Delet Case Is = "" 'blank cell valu Selection.EntireRow.Delet End Selec ActiveCell.Offset(1, 0).Selec x = x + Loo End Su Sub ChangingCredit( Dim credit As Strin Range("C7").Select 'starting poin Do Until x = 10 'Change number for extra number of blanks between dat rng = ActiveCel credit = ActiveCell.Offset(0, 1).Range("A1").Value 'assuming Cr right of numbe If credit = "CR" The ActiveCell.Value = rng * - End I ActiveCell.Offset(1, 0).Selec x = x + Loo End Su ----- Steve wrote: ---- I'm okay with formulas in excel, but have never done much on macros or vb. I now have an issue where I'm pretty sure I will need a more complext macro than my skills at this point. I have a large file that is imported from an ERP type application to excel. The file is generally around 25000-35000 rows. I'm trying to get the data cleaned up to enable sorting, custom filtering and pivot table creation. There few key things I'm trying to automate A: Deleting rows that have certain text in them. Generally, the text would be in the same column. For the most part these are subtotal type rows, but not always B. Formatting a cell based on the text value of a cell beside it. Specifically, I have cell that has the $ amount and is formatted as a number (e.g. 5416.38), but in the column beside it is a blank cell or the text "CR" (i.e. credit). What I want to do is if the cell is CR then format the number cell as a negative number (e.g. -5416.38) There are hundreds of these rows and thousands of these CR entries Thanks...steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows based on text in cell & formatting cell based on text in column beside it
Sorry about the other coding. I hope this helps out
Sub DeletingRows( Range("A1").Select 'Assumes the value used for deleting row is in column A 'change value to column where value is located Do Until x = 10 'Change number for extra number of blanks between dat caseText = ActiveCell.Valu Select Case (caseText Case Is = "Sub Total", "sub total" 'You can change to any value you wish to delet Selection.EntireRow.Delet y = Case Is = "Total", "total" 'You can add as many Case is ="TEXT" line as neede Selection.EntireRow.Delet y = Case Is = "" 'blank cell valu Selection.EntireRow.Delet y = End Selec If y = 1 The Els ActiveCell.Offset(1, 0).Selec End I If ActiveCell = "" The x = x + Els x = End I y = Loo End Su Sub ChangingCredit( Dim credit As Strin Range("B1").Select 'starting poin Do Until x = 10 'Change number for extra number of blanks between dat rng = ActiveCel credit = ActiveCell.Offset(0, 1).Range("A1").Value 'assuming Cr right of numbe If credit = "CR" Or credit = "cr" Or credit = "Cr" The ActiveCell.Value = rng * - End I ActiveCell.Offset(1, 0).Selec If ActiveCell = "" The x = x + Els x = End I Loo End Su |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows based on text in cell & formatting cell based on text in column beside it
David/Ro
Thanks so much. They both worked wonderfully. I'm amazed at the expertise on this site and the knowledge I gain every time I ask for help. Thanks for sharing yours Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting based on text in another cell | Excel Discussion (Misc queries) | |||
Conditional formatting based off cell with text? (i.e. "less than | Excel Discussion (Misc queries) | |||
Conditional formatting Based on cell A text with conditions in Cell B | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Conditional formatting of an entire row based on a cell text entry | Excel Discussion (Misc queries) |