Delete Rows Based on the Value in Certain Column & Delete Columns For Only Some Rows

[excel6000]

I have a spreadsheet where there is already data in rows 3 through 39, with rows 1 and 2 being the headers. I will be copying and pasting from another spreadsheet where the amount of data (number of rows) will differ every time I do the copy.

I need to be able to run a macro which will only look at the data which I copy over (at the moment it is from row 40 downwards) and then within that data, I need to delete all rows which do not have "& Total" in column F. After this, I need to take the remaining data and delete columns F & G and shift everything over to the left. However, it should not delete any data above where I started copying from (at the moment it is from row 39 upwards).

Any help is much appreciated.

 

[Arithos]

WOW!!! Thank you Arithos and everyone who helped. It works like a charm on my end too now!! I LOVE THIS WEBSITE..... AND I LOVE EXCEL!!!!

THATS the spirit! :D

Glad I was able to help!! Hope it saves you ALOT of tiresome work!!


PS: I keep editing all the time :S so keep looking at the post until all my 10 minutes are out, lol

 

[mrmmickle1]

Your very welcome. Keep coming back!

 

[Arithos]

Right, since i'm a bit of a perfectionist I want your code to be like this!!

Sub Del_Lines_Div1()
Dim LR As Long
Dim TR As Long
Dim r As Integer
Dim s1 As Worksheet

[COLOR=#FF0000]Application.ScreenUpdating = False[/COLOR]

Set s1 = Sheets(1)
LR = s1.Cells(Rows.Count, "F").End(xlUp).Row

On Error GoTo stahp    ' sets an errorhandling to react to no info in the inputbox
TR = InputBox("Please Enter the last row of the data you want to keep") + 1
On Error GoTo 0    'sets the errorhandling back to normal

For r = LR To TR Step -1
    If Cells(r, 6) <> "& Total" Then
    Rows(r).Delete
    End If
Next

LR = s1.Cells(Rows.Count, "F").End(xlUp).Row
Range(Cells(TR, 6), Cells(LR, 7)).Delete
Range("M" & TR & ":BA" & LR).Delete
Range("N" & TR & ":AJ" & LR).Delete

stahp:   'you hopped here if no info in the inputbox =) and the macro ends and all is well in macro paradise

[COLOR=#ff0000]Application.ScreenUpdating = True[/COLOR]

End Sub

This is what I always do anyway.
Then the Macro will seem to Magically happen =)

 

[excel6000]

So I ran into a problem. Everything worked fine in the spreadsheet where the data only went down to row 39, but when I put the macro into the main workbook which I'm working with, it deleted lots of stuff which it shouldn't have. See the following link for my spreadsheet:

https://drive.google.com/file/d/0B2VUNd4vLUgvd2RWYlk3RDBwUFU/edit?usp=sharing

The macro is named "Del_Rows_Div1"

 

[Arithos]

So I ran into a problem. Everything worked fine in the spreadsheet where the data only went down to row 39, but when I put the macro into the main workbook which I'm working with, it deleted lots of stuff which it shouldn't have. See the following link for my spreadsheet:

https://drive.google.com/file/d/0B2VUNd4vLUgvd2RWYlk3RDBwUFU/edit?usp=sharing

The macro is named "Del_Rows_Div1"

Yeah, it does that since it operates on the assumption that you will always do the sorting in sheet no1, if you want it do always run on sheet"Div 1" then change like this.

Set s1 = Sheets("Div 1")

'

and it works

PS: Plz look at my last macro, it will make this quicker

 

[excel6000]

Ha ha.... yeah, I just figured that out and was typing a message to say never mind.

Also, what does the application.screenupdating due??

 

[Arithos]

Ha ha.... yeah, I just figured that out and was typing a message to say never mind.

Also, what does the application.screenupdating due??

Now you have to "watch" as excel does everything, the screenupdating hides all the calculations, and displays them when you want to again (normally at the end of a macro). Its a must if you want your macro to operate quickly =)

 

[excel6000]

Thank you!!! I love it!!

 

[Arithos]

Thank you!!! I love it!!

Thahts what I like to hear, =)

now, if you could "Like" the solution that worked that would be perfect =)