Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA to Clear Contents of Rows By Date (Starting At A5)
Hi,
I have a list which begins in row 5. In column "A" there is a date followed by data in 9 more columns in that row. Before Macro: Date Comment 06-01-09 (cell A5) Expired (cell B5) 07-01-09 (cell A6) Expired (cell B6) I want a macro or VBA code that will take a range (say A5:J500) and clear the contents of all rows (starting in row 5) which have a column A: date 06-30-09 (if I could use a cell reference for this date it would be even better). After Macro: Date Comment 06-01-09 (cell A5) Expired (cell B5) I don't want to record a filter and use that in a macro because a filter hides the rows with data. This causes a problem as I have another routine that copies into those rows when the spreadsheet is updated and then performs a sort, I need to have all rows visible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA to Clear Contents of Rows By Date (Starting At A5)
Please don't reply, I figured it out (didn't have much time and one
problem was fixed by repeating a step): 'Clear contents of monthly data for refreshing Range("A5:J1000").Select Selection.ClearContents 'Copy data from all worksheets starting in cell A5 to master tab Dim sht As Worksheet Dim target As Worksheet Dim rng As Range Dim targetrng As Range 'You can change "Master" to your exact merge sheet name (tab name for the sheet) Set target = ActiveWorkbook.Worksheets("Master") For Each sht In ActiveWorkbook.Worksheets If Not sht.Name = target.Name Then 'Set rng = sht.UsedRange Set rng = sht.Range("a5:J500") Set targetrng = target.Cells(65536, 1).End(xlUp).Offset(5) rng.Copy targetrng End If Next sht Range("A5:J500").Select 'Run sort routine by date on list ActiveWorkbook.Worksheets("Master").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Master").Sort.SortField s.Add Key:=Range ("A5:A500") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Master").Sort .SetRange Range("A5:J500") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Dim c As Range, compdate As Date compdate = Range("A1").Value If IsDate(compdate) Then For Each c In Range("A5:A500", Range("K65536").End(xlUp)) If IsDate(c.Value) And c.Value compdate Then c.EntireRow.Delete End If Next End If If IsDate(compdate) Then For Each c In Range("A5:A500", Range("K65536").End(xlUp)) If IsDate(c.Value) And c.Value compdate Then c.EntireRow.Delete End If Next End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to clear contents and put an X | Excel Discussion (Misc queries) | |||
Clear Contents Macro | Excel Programming | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming | |||
Clear contents macro | Excel Programming | |||
Clear Contents Macro | Excel Worksheet Functions |