VBA has started deleting First column of the table - Excel 2019

SanjayGulatiMusafir

Board Regular
Joined
Sep 7, 2018
Messages
69
Dear Experts
Until yesterday the following code was working well for me. But for some reasons it has started behaving strange and deletes the column "Date" from the table. I'm unable to identify the problem so have stoped using the delete command for now. But need your help to find and rectify what's and why is going wrong way.

The purpose of this code is - To go to any blank row (no data) at the bottom of the table and delete the row if any such row exists, else it should go to a particular cell in "Date" column as per the date

Rich (BB code):
'Table is sorted with dates ascending before this command line
    Range("SBISGM[[#Headers],[Date]]").Select
    
    'To go to the bottom of the table
    Selection.End(xlDown).Select
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select

    'To test and select absolutely blank row if any
    If Not Intersect(ActiveCell, ActiveSheet.ListObjects("SBISGM").DataBodyRange) Is Nothing Then
        Intersect(ActiveCell.EntireRow, ActiveSheet.ListObjects("SBISGM").DataBodyRange).Select
    Else
        For Each cell In ActiveSheet.Range("SBISGM[Date]")
            If cell.Value >= [Today()] And cell.Value < [Today()+15] Then
            cell.Select
            Exit For
            End If
        Next
    End If
        
    'To me problem seems in this command line, so stopped using it, after stopping this command line it works fine
    'Range(Selection, Selection.End(xlDown)).Delete

    'To find and delete any blank row that only contains table formulas but no other data
    Dim r1 As Range, rows1 As Long, i1 As Long
    Set r1 = ActiveSheet.Range("SBISGM")
    rows1 = r1.rows.Count
    For i1 = rows1 To 1 Step (-1)
      If WorksheetFunction.CountA(r1.rows(i1)) = 0 Then r1.rows(i1).Delete
    Next
    
    For Each cell In ActiveSheet.Range("SBISGM[Date]")
        If cell.Value >= [Today()] And cell.Value < [Today()+15] Then
        cell.Select
        Exit For
        End If
    Next
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top