Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-21-2013, 11:27 AM
joflow21 joflow21 is offline Deleting rows with specific criteria Windows XP Deleting rows with specific criteria Office 2010 32bit
Novice
Deleting rows with specific criteria
 
Join Date: Oct 2013
Posts: 8
joflow21 is on a distinguished road
Default Deleting rows with specific criteria

Hi, I have written a macro that combines sheet1 of all workbooks within a folder into one worksheet. I now need to figure out how to delete all of the headers (there are all the same), except for the 1st one, as well as all of the rows where column D is blank. Any suggestions? Thank you.
Reply With Quote
  #2  
Old 11-21-2013, 12:19 PM
BobBridges's Avatar
BobBridges BobBridges is offline Deleting rows with specific criteria Windows 7 64bit Deleting rows with specific criteria Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 699
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

How are the rows sorted, joflow? If all the headers are at the top, then it's a matter of a) figuring out how many rows are headers and then b) deleting all but the first one. Step b is easy; step a is only probably easy, depending on circumstances. For example, does your program already know how many worksheets it combined? If so (and if all the worksheets had the same number of header rows) then you don't have to wonder how many rows to delete. If your program doesn't know, then is there a blank line between the headers and the data? If not, then does you know that the headers rows always have a certain value in a certain column (eg is the header for row 1 "AcctNo" for every worksheet)? If so, you can detect how far down the header rows go by looking at that. If you're not sure of the content of the header rows, are they at least the same in every worksheet? If so, your program can 1) check the contents of a cell on row 1, then work down the rows checking that the same value is in row 2, row 3 and so on until it gets to a change, which it knows is the first data line.

If none of those will work, then you have to figure out some other way of knowing which rows are column headers.

As for the blank in column D, that's even easier. If your data is sorted on column D, then you can just pick out the range where col D is blank and delete that block. If it's not sorted that way, and you don't want your program to sort it that way, then your program will just have to run down all the rows, deleting the ones where D is blank.

When deleting rows, I usually find it's more convenient to work backward from the bottom than down from the top. The reason is this: Your program is presumably using a counter to look first at row 2, then 3, then 4 and so on. After each check of a row, it increments the counter (from 26 to 27, say), and checks the next row. But if you just deleted row 26, then the old row 27 becomes the new row 26; and if you then increment your counter to check row 27, you've skipped a row.

If you work backward, however, that doesn't apply. You check row 27, and it doesn't need to be deleted. Then you check 26, and it does. Your program deletes row 26, and the old row 27 now becomes row 26. But you don't care; you move on to row 25 and check it, and so on until you get to row 2.

Now, except for a few of the details, this is all pretty straightforward. I imagine you wouldn't have had to ask if you'd known how to program a loop counter in VBA. Is that the problem? You know (perhaps) how to delete a row, but not how to program a loop that goes around and around checking rows 29, 28, 27 and so on?
Reply With Quote
  #3  
Old 11-21-2013, 01:16 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Deleting rows with specific criteria Windows 7 64bit Deleting rows with specific criteria Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,715
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

Quote:
If it's not sorted that way, and you don't want your program to sort it that way, then your program will just have to run down all the rows, deleting the ones where D is blank.
A looping can take very long and is often ineffeicient, this small piece of code will delete all rows where cells in col D are blank. I leave it to you to add it to your code

Code:
Sub QuickCull()
On Error Resume Next
Columns("D").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 11-21-2013, 01:25 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Deleting rows with specific criteria Windows 7 64bit Deleting rows with specific criteria Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,715
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

The following link might also help you to not import unnecessary headers

http://www.ozgrid.com/VBA/exclude-headings.htm
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 11-21-2013, 01:57 PM
joflow21 joflow21 is offline Deleting rows with specific criteria Windows XP Deleting rows with specific criteria Office 2010 32bit
Novice
Deleting rows with specific criteria
 
Join Date: Oct 2013
Posts: 8
joflow21 is on a distinguished road
Default

Hi Bob, the number of workbooks that will be combined will change each time the user has to use it, so I would not be able to delete headers by counting them. Since all of the headers are exactly the same, I was hoping to have a code that would look for all of the rows that are equal to the first one and delete all of them, excluding the first one.
Reply With Quote
  #6  
Old 11-21-2013, 02:05 PM
joflow21 joflow21 is offline Deleting rows with specific criteria Windows XP Deleting rows with specific criteria Office 2010 32bit
Novice
Deleting rows with specific criteria
 
Join Date: Oct 2013
Posts: 8
joflow21 is on a distinguished road
Default

Thank you pecoflyer. How would I write it if I also want to delete rows where the value of column D is 0?
Reply With Quote
  #7  
Old 11-21-2013, 05:09 PM
BobBridges's Avatar
BobBridges BobBridges is offline Deleting rows with specific criteria Windows 7 64bit Deleting rows with specific criteria Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 699
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I'd never used that particular value of SpecialCells; sounds like just the ticket.

Joflow, the number of incoming worksheets may change every time; but since the program is doing the importing, surely it can count them as it happens?

But if the headers are all the same, this should work too:
Code:
Set so = TheSubjectWorksheet
jr = 1
val1 = so.Cells(jr, 1).Value 'column header for col A
Do
  jr = jr + 1
  Loop While so.Cells(jr, 1).Value = val1
Range(so.Cells(2, 1), so.Cells(jr - 1, 1).EntireRow.Delete
(I haven't testing this code; something like it should be right, though.)
Reply With Quote
  #8  
Old 11-22-2013, 01:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Deleting rows with specific criteria Windows 7 64bit Deleting rows with specific criteria Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,715
Pecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud ofPecoflyer has much to be proud of
Default

Quote:
Originally Posted by joflow21 View Post
Thank you pecoflyer. How would I write it if I also want to delete rows where the value of column D is 0?

Again, I'm no VBA man, but as far as I know the fastest way is to use the auto filter from within a macro. There are examples around.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 11-22-2013, 12:10 PM
joflow21 joflow21 is offline Deleting rows with specific criteria Windows XP Deleting rows with specific criteria Office 2010 32bit
Novice
Deleting rows with specific criteria
 
Join Date: Oct 2013
Posts: 8
joflow21 is on a distinguished road
Default

Thank you Bob. I did wind up using a different code since the value in column A would be the same in all of the headers. I just used:

Sub DeleteRepeatedHeader()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("A" & i).Value = "PLANID" Then Rows(i).Delete
Next i
End Sub
Reply With Quote
  #10  
Old 11-22-2013, 12:10 PM
joflow21 joflow21 is offline Deleting rows with specific criteria Windows XP Deleting rows with specific criteria Office 2010 32bit
Novice
Deleting rows with specific criteria
 
Join Date: Oct 2013
Posts: 8
joflow21 is on a distinguished road
Default

Thank you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting rows with specific criteria Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Print attachment when it arrive in specific folder with specific subject visha_1984 Outlook 1 01-30-2013 10:42 AM
Deleting rows with specific criteria Count rows and add blank rows accordingly Hoochtheseal Word VBA 1 01-29-2013 09:23 PM
Adding columns in specific rows only mhays Excel 5 01-17-2012 09:13 AM
Deleting rows with specific criteria Mail Merge to create specific number of table rows flackend Mail Merge 2 08-24-2011 11:49 AM

Other Forums: Access Forums

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


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