clearing a cell's contents based on another cells content

[cbye]

I'm trying to clear duplicate data out of cells. I have a VBA to clear columns A and C and keep the first instance. But because column B might be the same throughout the report, it will clear out what I want to keep. The only way I see to do it is to look at the same cell in column D and if it has "System Firmware" it should be kept. All other instances be cleared. So in my example I would like to keep B5 and B14 because they are the first instance. All others would be cleared. All of my reports will be different. Some may have 1 hostname. Others may have 5 different hostnames. I also posted the code I use to clear data out of columns A and C.

PERSONAL.XLSB
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4	Hostname	Machine Type	serial number	component	machine data	machine data	machine data	machine data	machine data	machine data	machine data
5	computer 1	1234	5678901	System Firmware	data	data	data	data	data	data	data
6	computer 1	1234	5678901	ent0	data	data	data	data	data	data	data
7	computer 1	1234	5678901	ent1	data	data	data	data	data	data	data
8	computer 1	1234	5678901	ent2	data	data	data	data	data	data	data
9	computer 1	1234	5678901	pdisk0	data	data	data	data	data	data	data
10	computer 1	1234	5678901	pdisk1	data	data	data	data	data	data	data
11	computer 1	1234	5678901	pdisk2	data	data	data	data	data	data	data
12	computer 1	1234	5678901	tape0	data	data	data	data	data	data	data
13	computer 1	1234	5678901	tape1	data	data	data	data	data	data	data
14	computer 2	1234	abc1234	System Firmware	data	data	data	data	data	data	data
15	computer 2	1234	abc1234	ent0	data	data	data	data	data	data	data
16	computer 2	1234	abc1234	ent1	data	data	data	data	data	data	data
17	computer 2	1234	abc1234	ent2	data	data	data	data	data	data	data
18	computer 2	1234	abc1234	pdisk0	data	data	data	data	data	data	data
19	computer 2	1234	abc1234	pdisk1	data	data	data	data	data	data	data
20	computer 2	1234	abc1234	pdisk2	data	data	data	data	data	data	data
21	computer 2	1234	abc1234	tape0	data	data	data	data	data	data	data
22	computer 2	1234	abc1234	tape1	data	data	data	data	data	data	data
Sheet

Sub RemoveRepeatingStringsA()

Dim BaseStr As String, CurrStr As String
Dim EndRow As Long

EndRow = Range("A" & Rows.Count).End(xlUp).Row
BaseStr = Range("A1").Value

Application.ScreenUpdating = False

For Iter = 2 To EndRow
CurrStr = Range("A" & Iter).Value
If CurrStr = BaseStr Then
Range("A" & Iter).Value = vbNullString
Else
BaseStr = Range("A" & Iter).Value
End If
Next Iter

Application.ScreenUpdating = True

RemoveRepeatingStringsB

End Sub

 

[kevin9999]

If I'm not misunderstanding you, the only Rows you want to keep would be 5 & 14 and delete the rest? If that's the case, there's several ways you could do it, here's just 2.

Sub ClearDupes1()
    Dim ws As Worksheet, lr As Long, lc As Long, rng As Range
    Set ws = Worksheets("Sheet1")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column
    
    Set rng = ws.Range(Cells(4, 1), ws.Cells(lr, lc))
    
    With rng
        .RemoveDuplicates Columns:=Array(1, 3), Header:=1
    End With
End Sub

Sub ClearDupes2()
    Dim ws As Worksheet, lr As Long, lc As Long, rng As Range
    Set ws = Worksheets("Sheet1")
    lr = ws.Cells.Find("*", , xlFormulas, , 1, 2).Row
    lc = ws.Cells.Find("*", , xlFormulas, , 2, 2).Column
    
    Set rng = ws.Range(Cells(4, 1), ws.Cells(lr, lc))
    
    With rng
        .AutoFilter 4, "<>System Firmware"
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[Alex Blakenburg]

@cbye, I am reading the requirement a little differently to @kevin9999, posting what you want the finished product to look like would have been helpful.
My interpretation is that you do now want to delete rows but want to have column A,B,C to look more like it would when you do a pivot table.
With repeating labels only appearing on the first occurrence. (slight variation for column B)

Personally I don't think it is a good idea because you currently have a database style layout which is ideal for Pivot tables, filtering, sorting and querying.

It doesn't sound you want to rely on the 1st of component being System Firmware. What fields make it a first occurrence ?
Is the first occurrence when either columns A or C change ? Although if C always changes when A changes you could rely on just C.
Are there other fields that you want to use to decide whether it is the first occurrence ?

I am wondering if your issue is just the order in which are you doing it.
You are calling delete B from the delete A routine.
Since delete B needs columns A & C to be intact, you need to run the delete B routine first then call A & C.

Sub RemoveRepeatingStrings_Control()
    Call RemoveRepeatingStringsB    'B has dependancies on A & C so do this first
    Call RemoveRepeatingStringsA
    Call RemoveRepeatingStringsC
End Sub

Sub RemoveRepeatingStringsB()

    Dim BaseStr As String, CurrStr As String
    Dim EndRow As Long, Iter As Long
    
    EndRow = Range("A" & Rows.Count).End(xlUp).Row
    BaseStr = Range("A1").Value
    
    Application.ScreenUpdating = False
    
    For Iter = 2 To EndRow
        CurrStr = Range("A" & Iter).Value & Range("C" & Iter).Value         'Criteria - change of A & C
        If CurrStr = BaseStr Then
            Range("B" & Iter).Value = vbNullString                          'Blank out B based on change in column A & C
        Else
            BaseStr = Range("A" & Iter).Value & Range("C" & Iter).Value     'Criteria - change of A & C
        End If
    Next Iter
    
    Application.ScreenUpdating = True
    
End Sub

 

[cbye]

Hi, thanks for both of your replies. Sorry for not sending my end result Im looking for. In the vba that I posted previous, this works well for columns A and C because in my reports the different machines will always have a different hostname and serial number. But in column B, it may have the same machine type. Because of this, the code that works for A and C will clear out the machine type cell. Also these reports I get will always be different in the fact that I may only have one hostname or I may have 10 different hostnames. But the machine type could be the same. Thats why I wanted to use the cell in column D, "System Firmware" because that shows me the start of the new machine. Hopefully with my finished product this helps with the explanation?

PERSONAL.XLSB
	H
28
Sheet

 

[cbye]

PERSONAL.XLSB
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4	Hostname	Machine Type	serial number	component	machine data	machine data	machine data	machine data	machine data	machine data	machine data
5	computer 1	1234	5678901	System Firmware	data	data	data	data	data	data	data
6				ent0	data	data	data	data	data	data	data
7				ent1	data	data	data	data	data	data	data
8				ent2	data	data	data	data	data	data	data
9				pdisk0	data	data	data	data	data	data	data
10				pdisk1	data	data	data	data	data	data	data
11				pdisk2	data	data	data	data	data	data	data
12				tape0	data	data	data	data	data	data	data
13				tape1	data	data	data	data	data	data	data
14	computer 2	1234	abc1234	System Firmware	data	data	data	data	data	data	data
15				ent0	data	data	data	data	data	data	data
16				ent1	data	data	data	data	data	data	data
17				ent2	data	data	data	data	data	data	data
18				pdisk0	data	data	data	data	data	data	data
19				pdisk1	data	data	data	data	data	data	data
20				pdisk2	data	data	data	data	data	data	data
21				tape0	data	data	data	data	data	data	data
22				tape1	data	data	data	data	data	data	data
23
Sheet

 

[cbye]

To add, in my example each machine has 9 rows of info. But this is not always the case. Some may only have 3 rows of info and some may have 15 rows of info. Im trying to create a vba that will cater to any circumstances.

 

[Alex Blakenburg]

Will you ever have a new System Server line with both the same Hostname AND serial number as the previous System Server line ?

If the answer is no, my code should have worked. What happened when you tried it ?

 

[cbye]

Alex your code works great! thanks. I have tried it on several reports and the works flawlessly. Sorry I thought that I didn't explain it properly initally. Thanks much.

 

[Alex Blakenburg]

Glad to hear it. Thanks for letting us know..