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

E

excel6000

Board Regular
Joined
Jul 2, 2014
Messages
61
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Hi There

Try this code in a copy of your workbook first.

Code: 
Sub ONLYNEWDATA()

Dim LastRow As Long
Dim LastCol As Long
Dim TopRow As Long
Dim r As Range

LastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

TopRow = InputBox("Please Enter the last row of actual data")

TopRow = TopRow + 1

For Each r In Range("F" & TopRow & ":F" & LastRow)
    If r.Value <> "& Total" Then Rows(r).Delete
Next r

Range("F" & TopRow + 1 & ":G" & LastRow).Delete Shift:=ToLeft

End Sub

a input box will show up to ask you the last row of your starting data. so the macro will only run from the next row.

Regards.
 
Upvote 0
First off, thank you very much for your help!!

When I ran the macro, it gave me a "Run-time error '13': Type mismatch" error box. When I click on "Debug", it highlights the "Rows(r).Delete" portion of the code.

Also, I know this might seem crazy, but at the end of the macro, I need it to delete the following columns.... F, G, Q through BC, and BE through CB, then shift everything to the left.

Thank you again in advance for your help.
 
Upvote 0
Sorry, missed a thing.

Code: 
Sub ONLYNEWDATA()

Dim LastRow As Long
Dim LastCol As Long
Dim TopRow As Long
Dim r As Range

LastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

TopRow = InputBox("Please Enter the last row of actual data")

TopRow = TopRow + 1

For Each r In Range("F" & TopRow & ":F" & LastRow)
    If r.Value = "& Total" Then Rows(r.Row).Delete
Next r

LastRow = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("F" & TopRow + 1 & ":BC" & LastRow).Delete
Range("BE" & TopRow + 1 & ":CB" & LastRow).Delete
End Sub

That should do the trick. I added the new column you wanted to be deleted as well
 
Upvote 0
excel6000 said:
First off, thank you very much for your help!!

When I ran the macro, it gave me a "Run-time error '13': Type mismatch" error box. When I click on "Debug", it highlights the "Rows(r).Delete" portion of the code.

Also, I know this might seem crazy, but at the end of the macro, I need it to delete the following columns.... F, G, Q through BC, and BE through CB, then shift everything to the left.

Thank you again in advance for your help.
Click to expand...

Its hard to understand here, You want to delete Column F and G, THEN, Q and all in between to BC, what you need to know is that when you have deleted F and G, Q is no longer Q, the info there has been shifted 2 columns to the left.... You see my point here? values that used to be in Q, are now in column H. However I assume you have thought of this ^^ and suggest this. ;)

@sastoka kept some of your code, hope that is ok :)


Code: 
Sub Del_Lines_Div1()

Dim LR As Long
Dim TR As Long
Dim r As Integer
Dim s1 As Worksheet

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

TR = InputBox("Please Enter the last row of the data you want to keep. In your example this is 39") + 1   [COLOR=#ff0000]'Here is where you type a number, not text, and it should be the last row of the data you want to keep....[/COLOR]

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

LR = s1.Cells(Rows.Count, "F").End(xlUp).Row
Range(Cells(TR, 6), Cells(LR, 7)).Delete      [COLOR=#0000cd]' This code does exactly what u want even through this line, read below for the possible mistakes.[/COLOR]
Range("Q" & TR & ":BE" & LR).Delete          [COLOR=#ff0000]' If you did not think if this problem, just figure it out, and change the new columns in theese parts =)[/COLOR]
Range("BE" & TR & ":CB" & LR).Delete


End Sub
 
Last edited:
Upvote 0
Arithos,
Thank you so much.... your code is awesome and it worked when I ran it. And yes, I understand about the deleting cells part at the end. I adjusted it and got everything working well.

The only problem is that this code is deleting the rows which contain "& Total". What I want it to do is delete all rows which do NOT contain "& Total" in column F. ??
 
Upvote 0
Try changing this line

Code: 
[COLOR=#0000ff]If[/COLOR] r.Value = "& Total" [COLOR=#0000ff]Then [/COLOR]Rows(r.Row).Delete

To:

Code: 
[COLOR=#0000ff]If[/COLOR] r.Value <> "& Total" [COLOR=#0000ff]Then[/COLOR] Rows(r.Row).Delete
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Delete row based on whether cell in alternate column in row has data
Replies
3
Views
81
LazyBug
LazyBug
P
  • Question
Need VBA to automatically copy paste rows based on dates of current month.
2
Replies
11
Views
193
pk970
P
P
  • Solved
vba delete Row based on
Replies
3
Views
240
nemmi69
nemmi69
Ashe
  • Solved
VBA to delete rows that DON'T contain certain text
Replies
4
Views
251
Joe4
Joe4
PIsabel
  • Question
past without blanks
Replies
8
Views
157
Alex Blakenburg
A

Forum statistics

Threads
1,214,643
Messages
6,120,707
Members
448,981
Latest member
recon11bucks

Share this page

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
Back
Top