Clear Text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
I have a file that imports data into columns A to C. If a row in column C is balank, then any text in Columns D & E must be cleared

See Eg Below

MX1 MR S BENNIE 119,950.00 Colette 01/09/2006
MX2 MR M W MARA 3,000.00 Colette 02/09/2006
MX3 MRS M JOUBERT 119,495.01 DaVE 03/09/2006
Peter
Stephen


As can be seen Stphen & Peter mustr be cleared

Your assistance will be most appreciated


Howard
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Can you not filter by non-blanks then copy the remaining data?
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
How is the data imported ?.

Do you wish this is done at the time of import, or as an action you invoke ?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
The data is imported from a database main frame using VBA.

I would like to set up VBA code that looks a a row in column C, if the row in column C has a value, then nothing is nothing. If the row in column C is blank, then the text in column D & E must be cleared.

If you are uncertain as to what I mean, please provide me with your email address & I will send you my workfile.

Regards

Howard
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

how about

Code:
Sub clear_c_d()
For MY_ROWS = 1 To Sheets("Sheet1").Range("C65536").End(xlUp).Row
If IsEmpty(Range("C" & MY_ROWS)) Then
    Range("D" & MY_ROWS & ":E" & MY_ROWS).ClearContents
End If
Next MY_ROWS
End Sub

change refs as required.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,665
Office Version
  1. 2019
Platform
  1. Windows
Hi Only A Drafter

The code is working perfectly. Are there any VBA books, CD's that you can recommend I use. Trying to improve my VBA knowledge.

Also do you mind providing me with your email address as I have a spreadsheet that is linked to another spreadheet and I have written VBA code to replace the word Template with a date that is inputted via an InputBox into Cell E1, but cannot get the code to work.

I would need to send both files for you to look at with a full explanation of what I require you to do.

Kind regards

Howard
 

Watch MrExcel Video

Forum statistics

Threads
1,114,374
Messages
5,547,571
Members
410,798
Latest member
Candyman8019
Top