Find function in VBA

[zackii]

I've tried for a few hours but have failed to accomplish what I am after so any help would be greatly appreciated PLEASE.

Here's the data that I am working with (details follow after the table):

Activity Code	Description
Boston_Corporate <tbody> </tbody>	T100
Boston_Corporate	T2EQ
Oahu_PVP <tbody> </tbody>	T2w
Oahu_PVP	T3q
Oahu_PVP	T585
Oahu_PVP	T6qw
Oahu_PVP	T7w
Overhead_General <tbody> </tbody>	T8ty
Overhead_General <tbody> </tbody>	T9af
Overhead_General <tbody> </tbody>	T10855

<tbody>
</tbody>

The first row is the header row.
I am trying to write a macro that;

1. Searches for the first UNIQUE value in Column A (it's Boston_Corporate right now) and then add a blank row after that.
2. Searches for the second UNIQUE value in Column A (it's Oahu_PVP right now) and then add a blank row after that.
3. Searches for the third UNIQUE value in Column A (it's Overhead_General right now) and then add a blank row after that.
4. The macro continues to do so until it has 'actioned' all unique values in Column A

Worth mentioning:
The data will always be sorted by Column A beforehand.
There may be 100s of rows involved with hundreds of unique values in Column A so values cannot be hardcoded in the macro (need to be dynamic)

 

[Rick Rothstein]

Re: Need help with the Find function in VBA

@Rick Rothstein; how can I modify that code such that it still works if there are only 2 rows of data, the first row being the header row. Please see below:

Activity Code	Description
Boston_Corporate	T100

<tbody>
</tbody>

It should still merge cell A1 and A2 please.

Again, thanks a million for all the help so far.

Well, that is annoying... if there are multiple areas, then the first area is respected, but if there is only one area, it is changed to the CurrentRegion. The following code should work around the problem...

[table="width: 500"]
[tr]
	[td]Sub MergeCells()  Dim Ar As Range
  For Each Ar In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Areas
    If Split(Ar.Address(0, 0), ":")(0) = "A1" Then Set Ar = Range("A2")
    If Ar.Count > 1 Then Ar.Offset(1).Resize(Ar.Count - 1).ClearContents
    Ar.Resize(Ar.Count + 1).Merge
    Ar.HorizontalAlignment = xlCenter
    Ar.VerticalAlignment = xlCenter
  Next
End Sub[/td]
[/tr]
[/table]

 

[zackii]

Re: Need help with the Find function in VBA

Well, that is annoying... if there are multiple areas, then the first area is respected, but if there is only one area, it is changed to the CurrentRegion. The following code should work around the problem...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MergeCells()  Dim Ar As Range
  For Each Ar In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Areas
    If Split(Ar.Address(0, 0), ":")(0) = "A1" Then Set Ar = Range("A2")
    If Ar.Count > 1 Then Ar.Offset(1).Resize(Ar.Count - 1).ClearContents
    Ar.Resize(Ar.Count + 1).Merge
    Ar.HorizontalAlignment = xlCenter
    Ar.VerticalAlignment = xlCenter
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

@Rick Rothstein, sorry but that code still does not merge cells A2 and A3, if there are only 2 rows of data, the first row being the header row

Any ideas why?

 

[Rick Rothstein]

Re: Need help with the Find function in VBA

@Rick Rothstein, sorry but that code still does not merge cells A2 and A3, if there are only 2 rows of data, the first row being the header row

Any ideas why?

I am not sure what to tell you as it works correctly for me.

Can you describe to me what my latest code actually does for you, if anything?

 

[zackii]

Re: Need help with the Find function in VBA

I am not sure what to tell you as it works correctly for me.

Can you describe to me what my latest code actually does for you, if anything?

Disregard my last comment please. The code is working perfectly and is doing what it is supposed to do. Thanks again for all your help. Truly appreciated.