Sheet Cleanup Help Please

B

BlueAngel

I have a sheet that has formatting pre set for Columns A:BM. My data key
will be row 1. I need to clear all data in all rows to the right of where
row 1 data ends.

Example:


Col A Col B Col c Col D Col E
Row
1 Data Data Data


Data population ends in Cell C1, Clear all data D:BM.


Col A Col B Col c Col D Col E
Row
1 Data Data Data Data

Data population end in Cell D1, Clear all data E:BM

Can someone help with VBA that will automatically clear this?

Thanks in advance,
 
J

Jacob Skaria

Hello again, try the below.

Sub Macro()

Dim lngLastCol1 As Long, lngLastCol2 As Long

lngLastCol1 = Cells(1, Columns.Count).End(xlToLeft).Column
lngLastCol2 = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If lngLastCol2 > lngLastCol1 Then
Columns(lngLastCol1 + 1).Resize(, _
lngLastCol2 - lngLastCol1).ClearContents
End If

End Sub
 
B

BlueAngel

Hello to you.

I couldn't get this to work. Inserted a new module with your code deleted
Cell H1 and run the Macro and cannot see that anything has been cleared. Any
ideas on what I might be doing wrong?
 
J

Jacob Skaria

The below macro will clear the contents..If you want to delete the columns
then replace

..ClearContents

with

..Delete
 
R

Rick Rothstein

Does this macro do what you want?

Sub ClearAfterRowOneData()
Dim LastRowOneColumn As Long
LastRowOneColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(LastRowOneColumn + 1).Resize(, 65 - LastRowOneColumn).Clear
End Sub
 
R

Rick Rothstein

Perhaps I should mention, for clarity sake, that the number 65 in the Resize
property call is the numeric value for Column BM (which was the ending
column that the OP indicated the data should be cleared up to).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top