#16
|
|||
|
|||
Not tested. Run this after all the other code has finished doing it's thing.
Maybe to test it, run it as a separate macro. Code:
Sub jolivanes() Dim lr As Long, lc As Long, sh As Worksheet Application.ScreenUpdating = False For Each sh In ActiveWorkbook.Sheets If sh.Name<>"Extended Default" And sh.Name<>"Failed Default" Then '<----- Check spelling and capitalizing of sheet names. With sh lr = .Cells(.Rows.Count, 4).End(xlUp).Row lc = .Cells(1, .Columns.Count).End(xlToLeft).Column .Range(.Cells(1, 1), .Cells(lr, lc)).AutoFilter 4, "<11" .Range(.Cells(2, 1), .Cells(lr, lc)).SpecialCells(12).EntireRow.Delete End With sh.AutoFilterMode = False End If Next sh Application.ScreenUpdating = True End Sub |
#17
|
|||
|
|||
Remove Rows Less than 10
Hi Joli,
I put it at the end of the code and go an error. It didn't remove the rows in any of the worksheets. .Range(.Cells(1, 1), .Cells(lr, lc)).AutoFilter 4, "<11" Thanks for trying. I'll keep working on it. I did find another way to do it. Probably not the best way. I set up conditional formulas on each sheet that highlights anything in Yellow if the Age column is less than 11. Then I ran this code on one sheet that I found on the net: Code:
Sub DeleteRowslessthan10() Dim rg As Range Dim i As Long On Error Resume Next Set rg = Application.InputBox("Please select a single column range of cells." & vbLf & _ "If value is Less than 10,that row will be deleted.", _ Default:="D2:D3000", Type:=8) On Error GoTo 0 Application.ScreenUpdating = False If Not rg Is Nothing Then Set rg = Intersect(rg, ActiveSheet.UsedRange) If Not rg Is Nothing Then For i = rg.Rows.Count To 1 Step -1 If IsError(rg.Cells(i, 1).Value) Then rg.Rows(i).EntireRow.Delete ElseIf UCase(rg.Cells(i, 1).Value) = "N/A" Then rg.Rows(i).EntireRow.Delete ElseIf rg.Cells(i, 1).Value = "" Then ElseIf IsNumeric(rg.Cells(i, 1)) Then If rg.Cells(i, 1).Value < 11 Then rg.Rows(i).EntireRow.Delete End If Next End If End If End Sub Thoughts ? Thanks in Advance GBaker |
#18
|
|||
|
|||
Can you attach a workbook after all the copying etc has been done and before you would run the code to delete the rows that have <11 in Column D.
All the sheets should have some info in it by then I assume. |
#19
|
|||
|
|||
Remove Rows Less than 10
Hi Joli,
I have attached a workbook after all the copying etc has been done and before the code to delete the rows. Notice on a few worksheets their is no data for Age = 10 or less. In this report their is nothing that is only 10 or less days in the data for Delayed Retirement Plans,No Advisable Assets and Delayed Transactions but eventually their will be so I need the script to include those worksheets. The sheets for Extended Default Enroll Deadlin and Failed Default Enroll don't have an Age field so I don't need the them to be included in the script. Hope this helps and thanks for all your effort. I feel confident that you will find a way to help me. gbaker |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Grouping table rows to prevent individual rows from breaking across pages | dennist77 | Word | 1 | 10-29-2013 11:39 PM |
Count rows and add blank rows accordingly | Hoochtheseal | Word VBA | 1 | 01-29-2013 09:23 PM |
rows in word? | j2b3 | Word Tables | 3 | 07-19-2012 03:59 PM |
merging rows and creating sub-rows | gib65 | Excel | 2 | 12-09-2011 02:09 PM |
How to remove blank rows from a specified range? | Learner7 | Excel | 1 | 04-19-2011 02:45 AM |