Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Remove rows less than 10 Remove rows less than 10
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
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
  #2  
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: 91
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
  #3  
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
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Hybrid Mode Hybrid Mode


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 08:47 PM.

Contact Us - Privacy Statement - Top

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