Using Macros to delete cells & rows based on specific criteria

[schnellsls]

Hello,

Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.

I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.

Next, in column C, I have...

Employee2's Name
Employee2's Number
SPACE
Employee3's Name
Employee3's Number
SPACE
SUB TOTAL
SPACE
SPACE
SUB TOTAL
SPACE
(and this repeats over and over... the number of employees may be more or less)

I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.

So, all I would have left in column C is

SUB TOTAL
SPACE

Please let me know if you require any more specific information.

Thank you very much for your help.

 

[circledchicken]

Hi and welcome,

I' not sure I understand the second part of your question, but as an example say you wanted to find all the number 1's in column B and clear the associated column C cells - you might do something like this:

Sub example()

Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer

iLoop = WorksheetFunction.CountIf(Columns(2), 1)
Set rNa = Range("B1")
For i = 1 To iLoop
    Set rNa = Columns(2).Find(What:=1, After:=rNa, _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True)
    rNa.Offset(0, 1).Clear
Next i

End Sub

There are many other great examples here:
http://www.ozgrid.com/VBA/VBALoops.htm

 

[schnellsls]

Hi there,

The value I am trying to find will not be a 1, but it will be text in one instance, and numbers in another. Specifically, I am looking for "NV SUTA" and "29-23", and if those two are in the column B, everything in those associated rows to the right, (column's C through I) need to be deleted.

B C D E F G H I
NV SUTA X x x x x x x
29-23 X x x x x x x

(all of the X's in C:I, would need to be removed)

Hopefully that part is clearer.


Regarding the second part of this issue, is there a way for me to send you a screen shot of before and after using mock data to give you a better idea of what I am looking to achieve?

Thank you,

 

[circledchicken]

For the first part - you need to replace the 1's with the value your looking for, i.e. something like this (if its a string rather than a number you are looking for you need to wrap it in quotes as shown below):

Sub example()

Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer

iLoop = WorksheetFunction.CountIf(Columns(2), [B][COLOR="DarkRed"]"NV SUTA"[/COLOR][/B])
Set rNa = Range("B1")
For i = 1 To iLoop
    Set rNa = Columns(2).Find(What:=[B][COLOR="DarkRed"]"NV SUTA"[/COLOR][/B], After:=rNa, _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True)
    rNa.Offset(0, 1).Resize(1, 7).Clear ' this clears columns C to I. Changing the 7 to 8 for example would clear columns C to J
Next i

End Sub

Since you are looking for multiple values in column B, this way may be an option:

Sub example()

Dim vArr As Variant
Dim iLoop As Long
Dim rNa As Range
Dim i As Long, j As Long

vArr = Array([B][COLOR="DarkRed"]"NV SUTA", "29-23"[/COLOR][/B])
For j = 0 To UBound(vArr)
    iLoop = WorksheetFunction.CountIf(Columns(2), vArr(j))
    Set rNa = Range("B1")
    For i = 1 To iLoop
        Set rNa = Columns(2).Find(What:=vArr(j), After:=rNa, _
            LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True)
        rNa.Offset(0, 1).Resize(1, 7).clear
    Next i
Next j

End Sub

To post your sheet you can try (I copied these from the signature of Peter_SSs):

Borders-Copy-Paste - http://www.mrexcel.com/forum/showpost.php?p=2198045&postcount=2
Excel jeanie - http://www.excel-jeanie-html.de/index.php?f=1
MrExcel HTML Maker - https://skydrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

 

[schnellsls]

Hello,

That code you sent me works great! Thank you!

Regarding the next step...
I couldnt quite figure out how to put the excel file in the text. But I did find a work around. I am going to post a before and an after. Highlight and copy each into excel, you should easily see the difference between the two sets of data. The second does not include any Employee Information. Also, it only has one Sub Total per company. (Not two).



BEFORE
==============================================

DATE 1 PAGE:1
TIME CLIENT TAX BILLING REPORT

OVER LIMIT
COMPANY NAME TAX DESCRIPTION EMPLOYEE NAME GROSS TXBL SHLTRD WAGES WAGES BILLED EMPLOYER EMPLOYER
COMPANY ID TAX CODE EMPLOYEE ID GROSS TAX SHLTRD TAX TAX AMOUNT NET TAXABLE TAX ACCRUED

Company Number 1 NV SUTA Employee 1 123 123 123 123 123 123
111 29-24 12345678 123 123 123

Employee 2 123 123 123 123 123 123
12345679 123 123 123

Employee3 123 123 123 123 123 123
123456785
SUB-TOTALS: 123 123 123 123 123 123
123 123 123

SUB-TOTALS: 123 123 123 123 123 123
123 123 123
Company Number 2 NV SUTA Employee 1 123 123 123 123 123 123
112 29-24 12345677 123 123 123

Employee2 123 123 123 123 123 123
12345676
SUB-TOTALS: 123 123 123 123 123 123
123 123 123

SUB-TOTALS: 123 123 123 123 123 123
123 123 123






===============================================
AFTER
===============================================


DATE PAGE:1
TIME CLIENT TAX BILLING REPORT

OVER LIMIT
COMPANY NAME TAX DESCRIPTION EMPLOYEE NAME GROSS TXBL SHLTRD WAGES WAGES BILLED EMPLOYER EMPLOYER
COMPANY ID TAX CODE EMPLOYEE ID GROSS TAX SHLTRD TAX TAX AMOUNT NET TAXABLE TAX ACCRUED

Company Number 1 NV SUTA
111 29-24
SUB-TOTALS: 123 123 123 123 123 123
123 123 123
Company Number 2 NV SUTA
112 29-24
SUB-TOTALS: 123 123 123 123 123 123
123 123 123



================================================


Thank you for your help!

 

[schnellsls]

Whoops!

Okay that didnt work too well..

Here is the HTML of it via the HTML Jeanie...


===========================================
===========================================
BEFORE
===========================================
===========================================

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	DATE	1							PAGE:1
2	TIME		CLIENT TAX BILLING REPORT
3
4						OVER LIMIT
5	COMPANY NAME	TAX DESCRIPTION	EMPLOYEE NAME	GROSS TXBL SHLTRD WAGES		WAGES	BILLED	EMPLOYER	EMPLOYER
6	COMPANY ID	TAX CODE	EMPLOYEE ID	GROSS TAX	SHLTRD TAX	TAX	AMOUNT	NET TAXABLE	TAX ACCRUED
7
8	Company Number 1	NV SUTA	Employee 1	123	123	123	123	123	123
9	111	29-24	12345678	123	123	123
10
11			Employee 2	123	123	123	123	123	123
12			12345679	123	123	123
13
14			Employee3	123	123	123	123	123	123
15			123456785
16			SUB-TOTALS:	123	123	123	123	123	123
17				123	123	123
18
19			SUB-TOTALS:	123	123	123	123	123	123
20				123	123	123
21	Company Number 2	NV SUTA	Employee 1	123	123	123	123	123	123
22	112	29-24	12345677	123	123	123
23
24			Employee2	123	123	123	123	123	123
25			12345676
26			SUB-TOTALS:	123	123	123	123	123	123
27				123	123	123
28
29			SUB-TOTALS:	123	123	123	123	123	123
30				123	123	123
Sheet1

===========================================
===========================================
AFTER
===========================================
===========================================

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	DATE								PAGE:1
2	TIME		CLIENT TAX BILLING REPORT
3
4						OVER LIMIT
5	COMPANY NAME	TAX DESCRIPTION	EMPLOYEE NAME	GROSS TXBL SHLTRD WAGES		WAGES	BILLED	EMPLOYER	EMPLOYER
6	COMPANY ID	TAX CODE	EMPLOYEE ID	GROSS TAX	SHLTRD TAX	TAX	AMOUNT	NET TAXABLE	TAX ACCRUED
7
8	Company Number 1	NV SUTA
9	111	29-24
10			SUB-TOTALS:	123	123	123	123	123	123
11				123	123	123
12	Company Number 2	NV SUTA
13	112	29-24
14			SUB-TOTALS:	123	123	123	123	123	123
15				123	123	123
Sheet2

Thank you!

 

[schnellsls]

Hello,

I suppose I should explain the sheets in the previous post a bit more.
With the first bit of Macro help I received, I was able to get the contents of C:I removed to the right of column B’s NV SUTA and 29-24.
So therefore, in Column C, Employee 1’s information would not be there.
I need to delete the rows that are associated with Employee 2, Employee 3 and the first SUB TOTALS.
If you look in the second spreadsheet, (Sheet 2) you will see this change.
Is there a way I can write a Macro that will take care of this for me?
I don’t know how to write this, but here is what I was thinking… obviously my syntax is grotesquely wrong, but hopefully you get the idea of what I am trying to accomplish. This refers to cells / rows in sheet 1 from the previous post.
Sub Delete_Rows()

Dim r As Range

IF B = "NV SUTA"
'go up 3 rows
AND
IF B = "29-24"
'go down 1 row
= r
'call that a range
'this would refer to rows 10-18
Delete.r

Loop

End Sub

Thank you for your help!

 

[circledchicken]

Hi,

As you mentioned, assuming all Employee 1 information is now cleared, perhaps try the following (please test on a copy of your data first):

Sub example()

Dim ar As Range

For Each ar In Range("A8:A" & Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Areas
    ar.Resize(ar.Rows.Count - 2, 1).EntireRow.Delete
Next ar

End Sub

I learnt about the Areas method from a post by Peter_SSs here - http://www.mrexcel.com/forum/showthread.php?t=616991

 

[schnellsls]

Hello,

I tried running that code on my data set and for some reason there was an error.
MS Visual Basic:
Run-Time error '1004':
Application - defined or object - defined error

the options were END or DeBug.

When you click debug, i it highlights this in yellow:

ar.Resize(ar.Rows.Count - 2, 1).EntireRow.Delete

Any idea what I need to do in order to make this code work?

Thank you!

 

[circledchicken]

Hmmm...just tested it and it seems to work on your sample data.

Can you try it on a worksheet that looks exactly like the example you posted and let me know if it doesn't work on that either for you?