Clear Text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,135
Office Version
  1. 2021
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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,899
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
6,135
Office Version
  1. 2021
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
6,135
Office Version
  1. 2021
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,521
Members
431,883
Latest member
Hien

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top