Sheet Cleanup Help Please
in [Programming]
|
Prev: Macro to change column width and active window zoom as columns are selected
Next: Link error/Warning using macro in files converted to Excel 2007 (.
From: BlueAngel on 21 Apr 2010 10:01 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,
From: Jacob Skaria on 21 Apr 2010 10:10 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,
From: BlueAngel on 21 Apr 2010 10:21 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,
From: Jacob Skaria on 21 Apr 2010 10:27 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,
From: Rick Rothstein on 21 Apr 2010 13:01
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" <BlueAngel(a)discussions.microsoft.com> wrote in message news:0794F0F3-A73B-4ED9-A99C-9CE6D66B46F5(a)microsoft.com... > 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, |