How can I delete a set of rows?

gloryw

New Member
Joined
Jul 4, 2008
Messages
13
I thought I was pretty handy at excel, until I started going through the books and reading this forum. I am still way ahead of my co-workers, but I think that is not a statement of my awesome skills.
I have an ongoing task at work and I started reading the books to see if there was a faster way to do the same thing over and over, low and behold there is. I am making a resource database at work. we do not have access, we have excel 2003. I need to pull name, address, phone # from a variety of sources and organize it so we can print letters, lists, labels etc. Often the data for on contact takes up multiple rows when you do a large paste. I figured out how to get it all in one row(I use =trim(cell) to place the cell where i want then copy the fuction to all rows I need for each column. then I copy and paste special =values to remove the cell reference) but this was leaves rows with garbage data.(if ir takes up 2 rows it will be every other row that needs trashing, 3 rows the first row is good but the next two are trash, etc) Here is my question:
Is there a way to get excel to automatically remove the junk rows(ie if i select the section Im working on can I get excel to remove every second row?) or is there a better way of doing it so the junk rows dont happen?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Without really understanding how you are obtaining your data it is difficult to advise on avoiding 'junk' rows. The following macro will delete every second row. I've indicated where you can amend it to change that to every third row and so on. This works from the bottom up so if you have 24 rows it will delete rows 24, 22, 20 and so on.

Code:
Sub RemoveJunk()
Dim i As Long, LR As Long
Dim MyStep As Byte
MyStep = 2 '<<< change this to the number required e.g. 3 to delete every third row
LR = Range("A" & Rows.Count).End(xlUp).Row
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
For i = LR To 1 Step -MyStep
    Rows(i).EntireRow.Delete
Next i
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub
To use it press ALT + F11 to open the Visual Basic Editor then Insert > Module. Paste in the code above. Then close the code window using the X. Tools > Macro > Macros, highlight RemoveJunk and click the Run button. It is not reversible so I suggest that you run it on a copy of your sheet.
 
Last edited:

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
If I have a set o' data with junk rows, and I know that there is a column that should always contain a value (in an address list, this might be city or zipcode). Select the entire data set, filter out the junk rows, and delete them.
 

gloryw

New Member
Joined
Jul 4, 2008
Messages
13
Thanks so much! I have never used filter or macro it will be fun to try them.
 

gloryw

New Member
Joined
Jul 4, 2008
Messages
13

ADVERTISEMENT

I just tried both methods. There are problems with both. with the macro when i ran it it deleted the rows I wanted to keep and left the junk. Is there a way to make it count from the top down? The filter method works when there is nothing in one of the columns of the junk rows but if there is something in all rows it doesn't work. What I am working with is a variety of documents. sometimes It is from the web (query) some times it is cut and paste from other documents some times it is hand typed so telling you how I obtain the data, well its complicated. It all needs to fit in the same format though. For example one time the information was a webquery from superpages. another time it was an email that looked like this:

EDIT: deleted "real" information per Board policy - Can you post some sample data instead? - Moderator

without taking forever. This is only an example but hopefully it will help you help me. Thanks
 
Last edited by a moderator:

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
How can you make this:

Deleted - See above
<st1:street w:st="on"><st1:address w:st="on"></st1:address></st1:street>
<st1:street w:st="on"><st1:address w:st="on">Look like this?</st1:address></st1:street>
<st1:street w:st="on"><st1:address w:st="on"></st1:address></st1:street>
Deleted - See above

It doesn't look like the same data.
 
Last edited by a moderator:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Welcome to the Board!

Check out this post for How to post your sheet on the board.

If you can post some sample data for your various scenarios then I'm sure we'll get you some answers. Note that if you're pulling data from a bunch of internet sources, you'll likely have to be able to adapt code to a bunch of different situations. The good news is that'll get you proficient in that part of VBA real quick. :)
 

gloryw

New Member
Joined
Jul 4, 2008
Messages
13
To: Illiace(sp?) it wasnt the same data, but it needs to all be aligned the same way. company name in column a, address in b, city in c, province in d, postal code in e and phone # in f. However often what i work with will have company name , phone in a1 and address, city, province, postal code in a2. if im lucky. if im not it may be in 3 rows and information will be missing and ill have to pull info from some where else to fill in the gaps. what I have been doing is text to columns, delimit then using the formula =trim(cell) to move the data to where I want it. Coping the formula down all the rows is easy, then i copy and paste special - values in the same spot so i can delete without ruining my work. the problem comes from the deleting. If i am working with data that is 2 rows per entry then what i end up with is this:
a b c d
1A B C D
2B A D C
3A B C D

As you can see the 1st and 3rd rows are in the proper order but the 2nd row is junk. if it was only a couple of entries this wouldnt be a problem. but its usually a couple of hundred entries. and it isnt always evenly spaced. so the formula above is great if its even spaced or it can be reworked to count from line 1 instead of the last one, and filtering is great if there is a column where only the correct entries will have set data, but neither work right for what i currently have, no set data, not even entries, so Im hoping that there is a better way. either to get the data in order or to delete the lines that are wrong. 419 lines, that you only need half of is a pain in the butt, and once all the wonderful, helpful people here help me figure it out I wont have this problem in the future. Id rather take a little extra time now and save myself a lot of time later, than hen peck at it every time.Thanks again for the help so far
 

gloryw

New Member
Joined
Jul 4, 2008
Messages
13
Welcome to the Board!

Check out this post for How to post your sheet on the board.

If you can post some sample data for your various scenarios then I'm sure we'll get you some answers. Note that if you're pulling data from a bunch of internet sources, you'll likely have to be able to adapt code to a bunch of different situations. The good news is that'll get you proficient in that part of VBA real quick. :)


Thanks for the welcome. Sorry didnt realize that coping text available from a basic web search was no go, as its not private information( i got it from superpages). hopefully the sample I posted above will work. Im glad to hear that learning vba is easy. i think it will make my life easier.
Thank you
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Regardless of the source, we try to keep personal information off of the board just because it's the right thing to do. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,314
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top