clear sheet when new value found in range

[VeryForgetful]

Hi,

Can anyone assist with the code below please.

I'm looking to clear my destinatio sheet (excluding the header row) each time a new value is located in column A.

So for example, if I have the following account numbers:

12345
12345
12345
123456

It will list 3 rows for 12345 then as soon as it encounters a different account number the sheet will clear and the loop will start doing the same again for 123456 etc...

My code errors on accountCheck = TrgSheet.Cells.Find(what:=account, LookIn:=xlFormulas, lookat:=xlWhole)

Object variable or With block variable not set

Sub SplitData()


    Const NameCol = "A"
    Const HeaderRow = 1
    Const FirstRow = 2
    
    Dim SrcSheet As Worksheet
    Dim TrgSheet As Worksheet
    Dim SrcRow As Long
    Dim LastRow As Long
    Dim TrgRow As Long
    Dim account As String
    Dim accountCheck As Range
    
    'Application.ScreenUpdating = False
    
    Set SrcSheet = Sheet1
    Set TrgSheet = Sheet7
    LastRow = SrcSheet.Cells(SrcSheet.Rows.Count, NameCol).End(xlUp).Row
     
    For SrcRow = FirstRow To LastRow
        account = SrcSheet.Cells(SrcRow, NameCol).Value
        accountCheck = TrgSheet.Cells.Find(what:=account, LookIn:=xlFormulas, lookat:=xlWhole)
        
        If Not accountCheck Is Nothing Then
            Range(TrgSheet.UsedRange.Rows(2), TrgSheet.UsedRange.Rows(TrgSheet.UsedRange.Rows.Count)).Delete
        End If
        
        TrgSheet.Name = account
        TrgRow = TrgSheet.Cells(TrgSheet.Rows.Count, NameCol).End(xlUp).Row + 1
        SrcSheet.Rows(SrcRow).Copy Destination:=TrgSheet.Rows(TrgRow)
        Debug.Print account
        
        'rest of code here to..................
        
    Next SrcRow
    
    'Application.ScreenUpdating = True
    
End Sub

Thanks

 

[VeryForgetful]

Anyone please?

 

[tonyyy]

accountCheck = TrgSheet.Cells.Find(what:=account, LookIn:=xlFormulas, lookat:=xlWhole)

Since accountCheck is a Range object, you need to use a Set statement...

Set accountCheck = TrgSheet.Cells.Find(what:=account, LookIn:=xlFormulas, lookat:=xlWhole)

Cheers,

tonyyy