Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 25th 04, 08:21 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 141
Default 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   Report Post  
Old February 25th 04, 08:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,123
Default 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   Report Post  
Old February 25th 04, 09:06 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 153
Default 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   Report Post  
Old February 25th 04, 09:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 153
Default 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   Report Post  
Old February 26th 04, 03:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 141
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting based on text in another cell Dave Eade Excel Discussion (Misc queries) 1 January 23rd 09 03:59 PM
Conditional formatting based off cell with text? (i.e. "less than MikeV Excel Discussion (Misc queries) 3 February 13th 08 10:02 PM
Conditional formatting Based on cell A text with conditions in Cell B Raicomm Excel Discussion (Misc queries) 0 January 21st 08 04:46 PM
Conditional Formatting based on text within a cell w/ text AND num Shirley Excel Worksheet Functions 2 December 22nd 06 01:40 AM
Conditional formatting of an entire row based on a cell text entry Gilles Desjardins Excel Discussion (Misc queries) 2 December 22nd 05 11:48 AM


All times are GMT +1. The time now is 09:04 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017