Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-14-2014, 04:40 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

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


See example attached.
Attached Files
File Type: xlsm testtoremoverows.xlsm (52.7 KB, 11 views)
Reply With Quote
  #2  
Old 07-15-2014, 09:07 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 07-15-2014, 09:12 AM
BobBridges's Avatar
BobBridges BobBridges is offline Remove rows less than 10 Windows 7 64bit Remove rows less than 10 Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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 :-).
Reply With Quote
  #4  
Old 07-15-2014, 09:38 AM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 07-16-2014, 11:10 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

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
Attached Files
File Type: xlsm testtoremoverows_A.xlsm (57.5 KB, 9 views)
Reply With Quote
  #6  
Old 07-16-2014, 12:24 PM
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 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!!!!
Reply With Quote
  #7  
Old 07-16-2014, 02:06 PM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 07-16-2014, 02:46 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

@ 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
Attached Files
File Type: xlsm testtoremoverows_B.xlsm (185.9 KB, 8 views)
Reply With Quote
  #9  
Old 07-16-2014, 03:01 PM
WeDonNeedNoSteenkgRibbons's Avatar
WeDonNeedNoSteenkgRibbons WeDonNeedNoSteenkgRibbons is offline Remove rows less than 10 Windows XP Remove rows less than 10 Office 2003
Novice
 
Join Date: Jul 2014
Posts: 23
WeDonNeedNoSteenkgRibbons is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 07-16-2014, 03:45 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

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
Reply With Quote
  #11  
Old 07-17-2014, 08:01 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/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
Reply With Quote
  #12  
Old 07-17-2014, 09:47 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

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
Reply With Quote
  #13  
Old 07-17-2014, 10:54 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,
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!!!
Attached Files
File Type: xlsx JPM_Weekly_Exceptions_Data.xlsx (23.8 KB, 9 views)
Reply With Quote
  #14  
Old 07-17-2014, 02:15 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

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.
Reply With Quote
  #15  
Old 07-18-2014, 08:42 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,
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
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 02:47 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