Remove rows less than 10

gbaker · #1 07-14-2014, 04:40 AM

I need a Macro that will remove rows if they are Equal to or less than the number 10.
See example attached.

WeDonNeedNoSteenkgRibbons · #2 07-15-2014, 09:07 AM

Sometimes it's helpful to use macro recorder and "steal" from that. I'm not doing that specifically now, but it is how I learned ".EntireRow."

You'll need some wraparound code to cycle through your .UsedRange or some range (such as built from column D) or even Range("D: D"). (remove the space. I can't stop the forum processor from making an emoticon if I leave it in.) At any rate the main thing will be, assuming you have a cell represented by c with
dim c as range

then try this:
if (c.value<10) then c.entirerow.delete

Maybe you'll have
for each c in ...
where you specify the range with ages, as mentioned earlier.

BobBridges · #3 07-15-2014, 09:12 AM

Gbaker, in the attached sample the rows are sorted by the value you're using to determine whether the row should be deleted. Thus you can just delete the last n rows manually. There's no real reason you shouldn't write a macro to do it, but it may be more work than it's worth.

Possible answer #1: Yeah, but this seemed like a good example for me to use to start learning how to write macros. Excellent! I'll help.

Possible answer #2: In real life it won't always be sorted that way. Ok; in that case, how many rows are there likely to be? If it's only a few hundred it's no problem; if it's tens of thousands the macro may run a little slow. (Nothing like as slow as it would be to do it manually, of course.)

Possible answer #3: Oh, duh! Of course you're right. Never mind. I don't really expect that, but it's well to ask.

Possible answer #4: Something I didn't think of.

By the way, since this is a macro question it should be posted in the Excel-Programming forum rather than here. If another moderator doesn't beat me to it, I'll move it there as soon as I can remember how :-).

WeDonNeedNoSteenkgRibbons · #4 07-15-2014, 09:38 AM

If I beat the move

:

Bob's comments take precendence. I agree they are the "first line of attack" if the "possible answers" don't appply.

I neglected to mention that if you do "for each" with a range, you don't have to start from the end and work up, as some macro solutions require.

And Bob I'll use tags when I have more than a single line or so

jolivanes · #5 07-16-2014, 11:10 AM

Or like this

Code:

Sub jolivanes()
Dim lr As Long, lc As Long
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 4).End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
        With Range(Cells(1, 1), Cells(lr, lc))
            .AutoFilter 4, "<11"
            .Range(.Cells(2, 1), .Cells(lr, lc)).SpecialCells(12).EntireRow.Delete
        End With
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

gbaker · #6 07-16-2014, 12:24 PM

Hi Jolivanes,
This is fantastic. Work for one worksheet. The only problem is I need to do it in multiple worksheet with a workbook that I need to have this work and because I want to do 1 Macro I am getting an error message:

Compile Error: Duplicate declaration in current scope

Dim lr As Long, lc As Long

Any suggestions. I'm relatively new at VBA

Thanks in Advance for your help with this. It's a beginning!!!!

WeDonNeedNoSteenkgRibbons · #7 07-16-2014, 02:06 PM

I still recommend a specified (or named) range rather than hard coding the column number, and (with the latest code) relying on "end-up."

For your last question you need to be more explicit. Do you understand what scope means? Think of it as either a specified range, selection, procedure, module, or project. Consider this: have you noticed that you can't have consecutive statements that say
dim i as integer
but you can have two within a module?

With that in mind, does the duplicate declaration make sense? If not, just list all your code and indicate what is highlighted when you get the compile error.

Jumping ahead, you're probably going to want
for each sheet in sheets (or worksheets)
wrapped around your single sheet code. Does that approach fit your workbook?

jolivanes · #8 07-16-2014, 02:46 PM

@ novice.
I am still learning so I'd be really interested in an explanation on why not to use "End(xlUp)"
BTW, if gbaker want to find the absolute last used cell (in any column), that's no problem.

@gbaker
This works on all sheets in your workbook.
If that's not what you want, let us know which sheets to exclude.
Good luck.

Code:

Sub jolivanes()
    Dim lr As Long, lc As Long, sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Sheets
        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
    Next sh
    Application.ScreenUpdating = True
End Sub

WeDonNeedNoSteenkgRibbons · #9 07-16-2014, 03:01 PM

Joli, if there was a missing item in the middle, an empty cell, the End-up approach would only go that far. If you e.g.

Code:

dim c as range
for each c in range("AllAges")
etc.

or otherwise use range("AllAges"), then, as long as the range is correct, you'll get everything.

But I'll admit the using the filter and deleting that way is likely to be far far faster than walking every item in the range one by one.

On the other point, if you used the named range, there would be no hardcoded 4 in the code either.

jolivanes · #10 07-16-2014, 03:45 PM

Hi Novice.
I appreciate your explanation.
As they say, you're never too old to learn. And am I ever getting up there!!!

Quote:

Joli, if there was a missing item in the middle, an empty cell, the End-up approach would only go that far.

Do us a favor and fill a bunch of cells in Column A, leave some empty and run this

Code:

Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

As far as your named range is concerned, how would you make it variable in case more info is added to the bottom?

Quote:

if you do "for each" with a range, you don't have to start from the end and work up

I have never heard of this either.
Have you had a chance to try it?
I would like to see it but I have to go now (Happy Hour).
Let us know the result if you would have multiple rows, like rows 4, 5, 6 and 7, that need deleting.
Thanks again

gbaker · #11 07-17-2014, 08:01 AM

Hi Joli/Ribbons

Still having an issue trying to make the code work for multiple worksheets within one workbook. Here is the code I'm working with:

Code:

Option Explicit
Sub Importeverything()
'
' Importeverything Macro
'
'
    Application.ScreenUpdating = False
    Sheets("Holds").Select
    ChDir "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data"
    Workbooks.Open FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM_Weekly_Exceptions_Data.xlsx"
'Copies & Paste Holds information from Exceptions Data from Transaction Group to Master
    Sheets("Holds").Select
    Range("A2:H1076").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Holds").Select
    Range("A2").Select
    ActiveSheet.Paste
    
'Removes rows less than 10
    Call lessthan10
    
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Range("E25").Select
    
'Copies & Paste Delayed Retirement Plan information from Exceptions Data from Transaction Group to Master
    Sheets("Delayed Retirement Plans").Select
    Workbooks.OpenText FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM Retirement Plan.xls" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").ColumnWidth = 28.43
    Columns("G:G").ColumnWidth = 17.29
    Columns("F:F").ColumnWidth = 19
    Columns("E:E").ColumnWidth = 16.14
    Range("A2:H1076").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Delayed Retirement Plans").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
 'Removes rows less than 10
    Call lessthan10
    
'Closes JPM Retirement Plan sheet
    Windows("JPM Retirement Plan.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    
'Copies & Paste Revised Retirement Plan information from Exceptions Data from Transaction Group to Master
    Sheets("Revised Retirement Plan").Select
    Workbooks.OpenText FileName:= _
        "\\fngn.com\us\Projects\ProgramOps\Exceptions Masters & Data\JPM\Data\JPM Revised Retirement Plan.xls" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").ColumnWidth = 30.29
    Columns("G:G").ColumnWidth = 26.57
    Columns("F:F").ColumnWidth = 20
    Columns("E:E").ColumnWidth = 12.71
    Range("A2:H1260").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Revised Retirement Plan").Select
    Range("A2").Select
    ActiveSheet.Paste
    
 'Closes JPM Revised Retirement sheet
    Windows("JPM Revised Retirement Plan.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWorkbook.Close
    
 'Removes rows less than 10
    Call lessthan10
    
   
'Opens JPM Weekly Exceptions Data
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    
 'Copies & Paste No Advisable Assets information from Exceptions Data from Transaction Group to Master
    Sheets("No Advisable Assets").Select
    Range("A2:H1426").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("No Advisable Assets").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
 'Removes rows less than 10
     Call lessthan10
     
'Copies & Paste Delayed Transactions information from Exceptions Data from Transaction Group to Master
    Sheets("Delayed Transactions").Select
    Columns("G:G").ColumnWidth = 35
    Range("A5:G1202").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Delayed Transactions").Select
    Range("A5").Select
    ActiveSheet.Paste
    Range("A4").Select
    
'Removes rows less than 10
     Call lessthan10
  
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A4").Select
    
'Copies & Paste Extended Default Enroll Deadlin information from Exceptions Data from Transaction Group to Master
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Sheets("Extended Default Enroll Deadlin").Select
    Columns("A:A").EntireColumn.AutoFit
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Extended Default Enroll deadlin").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    
'Copies & Paste Failed Default Enroll Deadlin information from Exceptions Data from Transaction Group to Master
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Sheets("Failed Default Enroll").Select
    Selection.Copy
    Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("Failed Default Enroll").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    
'Removes rows less than 10
     Call lessthan10
    
'Closes Cut feature
    Windows("JPM_Weekly_Exceptions_Data.xlsx").Activate
    Application.CutCopyMode = False
    Sheets("Holds").Select
    ActiveWorkbook.Close
    
 Windows("JPM_Weekly_Exceptions_MASTER.xlsm").Activate
    Sheets("UPDATE").Select
    Range("B2").Select
End Sub

Thanks in Advance for all your help on this. I need it.
gbaker

jolivanes · #12 07-17-2014, 09:47 AM

Is the problem directly related to deleting rows with values of less than 10?
If not, it would be better to start a new thread where you can outline your specific problem.
When quickly glancing through your code in your last post, I noticed an abundance of "Select" being used.
Maybe have a read here about it.

http://stackoverflow.com/questions/1...cel-vba-macros

gbaker · #13 07-17-2014, 10:54 AM

Hi Joli,
Yes this is directly related. The code I sent opens various spreadsheets on the network and copies and pastes them to a Master spreadsheet. Each worksheet ends up with data. Anything line that has the number 10 of under needs to be deleted.
I'll attached a copy of the master for you to review.

If you look at the Holds Tab you will see under the column named Age their are a number of rows that have information 10 days or less. I need to automatically delete these rows. This will be the same for each Tab in the workbook. Would like it to be in one code so I can have a button that would make them work all at once along with the other things that happen in the code I sent you earlier!!!

jolivanes · #14 07-17-2014, 02:15 PM

Is the copying of information from the closed workbooks working?
If so, on which sheets do you want to run the code I supplied?
By the looks of it, not on all sheets like the code is designed to do.
If you have all the copying, pasting, deleting columns etc done, run the code on the sheets that need it only.

gbaker · #15 07-18-2014, 08:42 AM

Hi Joli,
All sheets have to be able to delete any rows that have 10 in the Age column except extended Default and Failed Default Tabs.

I even tried to use conditional formatting Column D2 (cell value between =1 and =10 format fill in Yellow.

Then tried to come up with a syntax like:

Code:

Range("D2:D1076").Select 
Do Until ActiveCell = ""
If ActiveCell.Interior.Color = vbYellow Then
ActiveCell.Rows.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Couldn't get that to work either. Let me know if you can get your code to work for me or why the code above won't work.
Thanks in Advance
GBaker

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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