Sheet Cleanup Help Please
 

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,

Sheet Cleanup Help Please
 

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


--
Jacob (MVP - Excel)


"BlueAngel" wrote:

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,

Sheet Cleanup Help Please
 

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?


"Jacob Skaria" wrote:

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


--
Jacob (MVP - Excel)


"BlueAngel" wrote:

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,

Sheet Cleanup Help Please
 

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

..ClearContents

with

..Delete

--
Jacob (MVP - Excel)


"BlueAngel" wrote:


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?


"Jacob Skaria" wrote:

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


--
Jacob (MVP - Excel)


"BlueAngel" wrote:

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,

Sheet Cleanup Help Please
 

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

--
Rick (MVP - Excel)



"BlueAngel" wrote in message
...
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,

Sheet Cleanup Help Please
 

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).

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)



"BlueAngel" wrote in message
...
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,