Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 07-18-2014, 10:19 AM
jolivanes jolivanes is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 93
jolivanes will become famous soon enough
Default

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
Reply With Quote
  #17  
Old 07-18-2014, 11:12 AM
gbaker gbaker is offline Remove rows less than 10 Windows 7 32bit Remove rows less than 10 Office 2010 32bit
Competent Performer
Remove rows less than 10
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
It works but I haven't tested it on all worksheets. I might try to use the Call feature. The only thing I don't like about it is it makes the user choose the column with a InputBox and I can't figure out how to change that so it just runs the code based on Coulumn 4.


Thoughts ?


Thanks in Advance
GBaker
Reply With Quote
  #18  
Old 07-18-2014, 12:36 PM
jolivanes jolivanes is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2007
Advanced Beginner
 
Join Date: Sep 2011
Posts: 93
jolivanes will become famous soon enough
Default

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.
Reply With Quote
  #19  
Old 07-21-2014, 04:51 AM
gbaker gbaker is offline Remove rows less than 10 Windows 7 32bit Remove rows less than 10 Office 2010 32bit
Competent Performer
Remove rows less than 10
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default 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
Attached Files
File Type: xlsm JPM_Weekly_Exceptions_lessthan10test.xlsm (490.9 KB, 9 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove rows less than 10 Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Remove rows less than 10 Count rows and add blank rows accordingly Hoochtheseal Word VBA 1 01-29-2013 09:23 PM
Remove rows less than 10 rows in word? j2b3 Word Tables 3 07-19-2012 03:59 PM
Remove rows less than 10 merging rows and creating sub-rows gib65 Excel 2 12-09-2011 02:09 PM
Remove rows less than 10 How to remove blank rows from a specified range? Learner7 Excel 1 04-19-2011 02:45 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:01 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft