Deleteng Lines

Eddie Wildon · Sep 18, 2006

Good morning everyone

I have a toughie that, after about 2 hours, I cannot seem to solve.

I have a worksheet spanning 54000 lines and need to weed out most of it to get at what I really want.

Several of the lines contain identical data but I cannot simply sort it because I need to maintain names above data.

Is there a script that I can use to look at each line, and if it contains certain text to delete the whole line, and then move on to the next line, all that way to the end of the report?

This will maintain the order that the data is in, while removeing unwanted lines. Any help on this would be appreciated.

JCA

Peter_SSs · Sep 18, 2006

Ken

Give this a try:

Sub DeleteCells()
 Dim LastRow As Long
 Dim Row As Long
 Dim Column As Integer
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 For Column = 2 To 20 Step 3
 LastRow = Cells(65536, Column).End(xlUp).Row
 For Row = LastRow To 1 Step -1
 If Cells(Row, Column).Value = 0 Then
 Range(Cells(Row, Column - 1), Cells(Row, Column + 1)).Delete Shift:=xlUp
 End If
 Next Row
 Next Column
 Application.ScreenUpdating = True
 Application.EnableEvents = True
End Sub

Peter_SSs · Sep 18, 2006

By the way, is this looking to solve the following thread?
http://www.mrexcel.com/board2/viewtopic.php?t=233360

calgaryguy · Sep 18, 2006

It is. I thought I would try this from a different angle. Thanks. I'll give this a try and see what happens. Thanks.

calgaryguy · Sep 18, 2006

So, I tried just copying and pasting the code into the "View Code" in the tab. Is that what I should have done or should I have done something different? I tried that but it didn't work. Thanks again for your help.

Ken

calgaryguy · Sep 18, 2006

I figured it out but now I have a couple other questions. Is there a way to have this constantly update? I'm also thinking that I probably want it to just hide instead of deleting. If a cell goes from 0 to 1, I would need it to display. As well, I need to keep the first two rows. Thanks.

Ken

Peter_SSs · Sep 19, 2006

Is there a way to have this constantly update?

Yes. This may need some modification to suit your particular sheet but try this as a starting point.

In the VBA code window, double-click on the particular sheet in the Project pane on the left. Then on the right side of the screen choose 'Worksheet' from the left drop-down and 'Change' from the right drop-down. Place the code between the two lines created from the above process. Any time the sheet changes, the code will be run. This will get you the 'constant update', but can sometimes cause nuisance (waiting for it to run when you don't want to etc).

As well, I need to keep the first two rows.

OK, in the code change the relevant line to;
For Row = LastRow To 3 Step -1
So, with both of the above steps completed, the code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LastRow As Long
 Dim Row As Long
 Dim Column As Integer
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 For Column = 2 To 20 Step 3
 LastRow = Cells(65536, Column).End(xlUp).Row
 For Row = LastRow To 3 Step -1
 If Cells(Row, Column).Value = 0 Then
 Range(Cells(Row, Column - 1), Cells(Row, Column + 1)).Delete Shift:=xlUp
 End If
 Next Row
 Next Column
 Application.ScreenUpdating = True
 Application.EnableEvents = True
End Sub

I'm also thinking that I probably want it to just hide instead of deleting. If a cell goes from 0 to 1, I would need it to display.

This is more of a problem because you can only hide whole rows or whole columns. One option might be to change the font colour to match the background colour or to custom format the cell in some other way so that the three cells appear empty when the middle value is 0. However, the cells themselves will still be visible and your table will just look like it has blanks in where the 0 values occur.

Somebody else on the board might have some other thoughts on how you might approach this.

If you need help with the 'blanking out' of the cells as I have suggested, post back and I (or somebody else) will give it a go.

calgaryguy · Sep 19, 2006

After thinking about this, I probably don't want this to constantly update. It would only work in the one direction. As far as blanking out the 0, someone else suggested that in one of the other posts.

I have two more questions. First off, I'm wondering if the code can be changed so that instead of deleting in the current worksheet, it would copy to a new worksheet and then do the delete? That way, when I make a change, I can just rerun this and it will create a new worksheet for me to use that is updated. I would also have a record of the old worksheet as well. Would that work?

The other idea I had was what about sorting so that any with 0 are at the bottom. I'm thinking that this might be more difficult since each day (with the 3 cells) would need to be sorted together. Plus there would be 7 different sections to sort.

The last question (which I guess is really a 3rd question now, sorry) is to add complexity to the above code. I have two weeks on one sheet. In between week 1 and week 2, I have two extra columns where I inserted the date so that it prints at the right (it's landscape). I would like to also include the range for week 2. It is in column Y, AB, AE, AH... I tried a few different ways to add in the second range but I wasn't successful. If I can do my first idea and just copy to a new worksheet before deleting, I would like to keep week 1 and 2 together.

Thanks again for all your help. This board has been really helpful in teaching me Excel. About 2 months ago, I wouldn't have even tried anything to do with VBA. I still don't know much about it but at least I know how to do a little bit.

Ken

Peter_SSs · Sep 19, 2006

Ken

I think that the copy to another sheet and delete can certainly be done. Unfortunately I cannot put the time to it at the moment. If somebody else does not resolve this for you before I do have the time, I will certainly give it a go for you then.

calgaryguy · Sep 19, 2006

Thanks! There is no rush to it.

Ken

Peter_SSs · Sep 20, 2006

Ken

Assuming your original sheet is named 'Original' and your results sheet is named 'Results', try the following code (or modify to suit what sheet names you have/want).

You said you maybe didn't want this automatic so I have just created a macro that would need to be triggered by a button for example.

Sub CopySheetAndDeleteCells()
 Dim LastRow As Long
 Dim Row As Long
 Dim Column As Integer
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Application.DisplayAlerts = False
 On Error Resume Next
 Sheets("Results").Delete
 Application.DisplayAlerts = True
 Sheets("Original").Copy Before:=Sheets("Original")
 Sheets("Original (2)").Name = "Results"
 Sheets("Results").Activate
 For Column = 2 To 20 Step 3
 LastRow = Cells(65536, Column).End(xlUp).Row
 For Row = LastRow To 3 Step -1
 If Cells(Row, Column).Value = 0 Then
 Range(Cells(Row, Column - 1), Cells(Row, Column + 1)).Delete Shift:=xlUp
 End If
 Next Row
 Next Column
 For Column = 25 To 43 Step 3
 LastRow = Cells(65536, Column).End(xlUp).Row
 For Row = LastRow To 3 Step -1
 If Cells(Row, Column).Value = 0 Then
 Range(Cells(Row, Column - 1), Cells(Row, Column + 1)).Delete Shift:=xlUp
 End If
 Next Row
 Next Column
 Application.ScreenUpdating = True
 Application.EnableEvents = True
End Sub

Deleteng Lines

Eddie Wildon

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

calgaryguy

Board Regular

calgaryguy

Board Regular

calgaryguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

calgaryguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

calgaryguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Deleteng Lines

Board Regular

MrExcel MVP, Moderator

Excel Facts

MrExcel MVP, Moderator

Board Regular

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock