Macro for deleting unwanted data from a database extract

chadski778

Active Member
Joined
Mar 14, 2010
Messages
297
Hi,

I carry out regular extracts from a database but a lot of the data is not required. I would like a macro that deletes all the rows between the following strings in my spreadsheet - 'Oilsafe Classification:' and 'Toxic Hazard Rating'; 'Our GHS Class:' and 'Regulatory Information'; 'ECN (Taiwan)' and 'REACh overall status'; 'REACh overall status' and 'Physical Properties'; 'Density at ambient' and 'Flash point (C)'; 'Flash point (C)' and 'WGK'; and every row containing data after 'WGK'

Is that possible?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Maybe something like this?

Gary

Code:
Public Sub Test()

Dim oSheet As Worksheet

Dim oStart As Range
Dim oEnd As Range

Set oSheet = ActiveSheet

'Repeat the following block for each pair of phrases
Set oStart = oSheet.UsedRange.Find(what:="Oilsafe Classification:", MatchCase:=False, lookat:=xlPart)
Set oEnd = oSheet.UsedRange.Find(what:="Toxic Hazard Rating", MatchCase:=False, lookat:=xlPart)

If Not oStart Is Nothing And Not oEnd Is Nothing Then
    'Change fill color in lieu of delete
    oSheet.Range(oStart.Offset(1, 0).Row & ":" & oEnd.Offset(-1, 0).Row).Interior.ColorIndex = 3
    'oSheet.Range(oStart.Offset(1, 0).Row & ":" & oEnd.Offset(-1, 0).Row).Delete 'Actually delete rows
End If
'End block repeat

End Sub
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,266
Members
452,902
Latest member
Knuddeluff

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