Looking for help with Excel Macro

SweNoob

New Member
Joined
Dec 16, 2016
Messages
1
Hi,

I have serveral very large documents with logged data that i want to sort out. But first i need to delete certain rows wich contains for example BE850 and BE410. its a long sting but those are the tag-names that i can use to delete.

Since there is over 800000 rows in each document and it would take forever to split down the files i wonder if there is any way i can get help to make an macro to find and delete those values for me.

I have tried serveral ready macros from different forums but none that can help me (as far as i can see) im no pro on VBA.
And i also understand that Excel don't really like big documents like this because it hangs up if i hapend to do it with some macros i have found but i guess there is something to not refres the screen.

All help would be apreciated since im no pro at this
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi SweNoob,

I am assuming that values BE850 and BE410 appear in column A, and that the file has 100 rows. Please modify these two parameters in the code below if necessary. I have also added "Application.ScreenUpdating = False" line. Hope it helps.

Code:
Sub delRows()Dim i As Integer
Dim j As String


Application.ScreenUpdating = False


For i = 1 To [B][COLOR=#ff0000]100[/COLOR][/B]
    j = Range("[COLOR=#ff0000][B]A[/B][/COLOR]" & i).Value
    If (j = "BE850" Or j = "BE410") Then
    Rows(i).Delete
    i = i - 1
    End If
Next i


End Sub
 
Upvote 0
I have serveral very large documents with logged data that i want to sort out. But first i need to delete certain rows wich contains for example BE850 and BE410. its a long sting but those are the tag-names that i can use to delete.
I assume 'sting' should have been 'String'. Does this mean that your target strings BE850 and BE410 embedded in larger text strings? Would only one column need to be searched for the target strings or are they scattered across a range of columns randomly?
 
Upvote 0
SweNoob,

Welcome to the MrExcel forum.

You did not say what column the strings are located in?

Here is a macro solution for you to consider that does not do any looping thru the rows in the active worksheet.


Sample raw data:


Excel 2007
A
1Title A
2I have serveral very large documents with logged data that i want to sort out.
3But first i need to delete certain rows wich contains for example BE850.
4Looking for help with Excel Macro
5Delete thia BE850 I think.
6But first i need to delete certain rows wich contains for example BE410.
7Please keep me.
8Delete thia BE410 I think.
9Please keep me, again.
10
Sheet1


And, after the macro:


Excel 2007
A
1Title A
2I have serveral very large documents with logged data that i want to sort out.
3Looking for help with Excel Macro
4Please keep me.
5Please keep me, again.
6
7
8
9
10
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub DeleteRowsWith_BE850_BE410()
' hiker95, 12/16/2016, ME981186
Application.ScreenUpdating = False
Columns("A").Replace "*BE850*", "#N/A", xlWhole
Columns("A").Replace "*BE410*", "#N/A", xlWhole
On Error Resume Next
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteRowsWith_BE850_BE410 macro.
 
Upvote 0
Try this:

Code:
Sub FilterMe()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With ActiveSheet.Range("A1:A" & Lastrow)
.AutoFilter Field:=1, Criteria1:=Array("*BE850*", "*BE410*"), Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Rows.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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