delete last cell data of all rows in last 3 tables

there are 4 tables
what I want is delete the last cell data of all rows  in last 3 tables
cells highlighted in yellow colour are the last cell data of last 3 tables
this highlighted colour is only for understanding purpose in actual file there is no highlighted colour
see the sample file
Book20.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
You may try something like this...
Sub DeleteLastCellInLastThreeTables()
Dim lr As Long
Dim Rng As Range, Cell As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row

For Each Rng In Range(Range("A1").End(xlDown).Offset(1), Range("A" & lr)).SpecialCells(xlCellTypeConstants, 3).Areas
    For Each Cell In Rng.Cells(1).Offset(1).Resize(Rng.Cells.Count - 1).Cells
        Cell.End(xlToRight).Clear
    Next Cell
Next Rng

End Sub

Open in new window

0
 
aikimarkCommented:
Sub Q_29098231()
    Dim wks As Worksheet
    Dim rng As Range
    Dim lngLoop As Long
    Dim lngRow As Long

    Set wks = Worksheets("sheet1")
    Set rng = wks.Cells(wks.Rows.Count, 1).End(xlUp)
    lngRow = rng.Row
    For lngLoop = 1 To 3
        wks.Rows(rng.Row).Delete
        Set rng = wks.Cells(lngRow, 1).End(xlUp)
        Set rng = rng.End(xlUp)
        Set rng = rng.End(xlUp)
        lngRow = rng.Row
    Next
End Sub

Open in new window

0
 
Avinash SinghAuthor Commented:
I want to delete the last cell of all rows with data in table 2,table3,table4
Kindly check the sample file i have highlighted what i want
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
aikimarkCommented:
Ah.  I mistook last cell to be the last row.
0
 
aikimarkCommented:
Sub Q_29098231()
    Dim wks As Worksheet
    Dim rng As Range
    Dim lngLoop As Long
    Dim lngTopRow As Long
    Dim lngRow As Long
    
    Set wks = Worksheets("sheet1")
    Set rng = wks.Cells(wks.Rows.Count, 1).End(xlUp)
    lngTopRow = rng.End(xlUp).Row
    
    Application.ScreenUpdating = False
    For lngLoop = 1 To 3
        For lngRow = rng.Row To lngTopRow + 1 Step -1
            wks.Cells(lngRow, wks.Columns.Count).End(xlToLeft).ClearContents
        Next
        Set rng = rng.End(xlUp).End(xlUp)
        lngTopRow = rng.End(xlUp).Row
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Avinash SinghAuthor Commented:
Thnx Subodh Sir for giving ur precious time to this post
and thnx aikimark for giving ur precious time to this post
problem solved
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash! Glad I could help.
0
 
aikimarkCommented:
@Avinash

Does your accepted solution only affect the last three sets of data or all sets?
0
 
Avinash SinghAuthor Commented:
my accepted solution only affect the last cell of all rows in last 3 tables
0
 
aikimarkCommented:
Will you ever have more than four 'tables' (sets of data)?  If so, retest your accepted code against five sets.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.