Clear contents of all cells in a row except 1 column

brianfosterblack

Active Member
Joined
Nov 1, 2011
Messages
251
I have a macro which steps through each row in column from E3 downwards. If it finds an entry (Text or number or date) in this column I then need a process to run to clear all the contents in that row except for column F which contains a formula. I do not want to delete the row - just clear its contents.
I am not sure if it is easier to clear contents for the entire row as only columns A to G have data in them in the worksheet.
Can anyone help I have looked for a solution but they all seem to delete the row
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
For example, not clearing the entire row

VBA Code:
Sub jec()
 For Each it In Range("E3", Cells(Rows.Count, 5).End(xlUp))
   If it.Value <> "" Then Union(it.Offset(, -4).Resize(, 5), it.Offset(, 2)).ClearContents
 Next
End Sub
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
You're welcome!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,001
Members
425,322
Latest member
galaxy6623top

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