How to delete rows using some criteria and writing a macro ?

[Aditya Jadhav]

Hello friends,

I'm pretty new to the macros but as i have started working in excel my job is getting difficult as i go through the data. i need to delete rows which only have date and time in the first column and the rest columns have other data, now the problem is i want to delete some unwanted rows from this data on certain criteria that if there is no data in the other columns the whole rows which have date and time mentioned it be deleted.

As the data set is huge it needs to have an automated way to do it hence asking for help.

I need help in sorting this out.

Attaching the file which i need to sort.

Regards,

Aditya

[#2]

Hi Aditya,

please find attached my solution using VBA. The macro text is:

Sub ConditionallyDeleteRows()

    Dim vaData As Variant
    Dim rData As Range
    Dim r As Long, c As Integer
    Dim iFirstDataRow As Integer
    Dim bDeleteRow As Boolean
   
    Set rData = Range(ActiveSheet.ListObjects(1).Name)
    vaData = rData
   
    iFirstDataRow = rData.Row
   
    For r = UBound(vaData, 1) To LBound(vaData, 1) Step -1
        bDeleteRow = False
        If IsDate(vaData(r, 1)) Then
            For c = 2 To UBound(vaData, 2)
                If Len(Trim(vaData(r, c))) Then
                    bDeleteRow = False
                    Exit For
                Else
                    bDeleteRow = True
                End If
            Next
           
            If bDeleteRow Then
                Rows(r + iFirstDataRow - 1).Delete
            End If
           
        End If
    Next
       
End Sub

I have tested it on a smaller set of data. Running it on your entire dataset will take a while.

Just to mention, that you can also use Power Query to achieve a similar solution.

Yury

[#3]

Dear Yury,

Been trying to run the macro you have provided but it takes too long to run. It makes excel very slow, you suggested to use Power Query and am working on that now to find a solution. Have never used power Query before but now its time to look into it.

Thanks for the help and support.

Regards,

Aditya

[#4 · Accepted solution]

Hi Aditya,

please find attached the Power Query solution. The resulting table is in the 'Result' tab. To refresh, please right click on any cell and select 'Refresh'. If you have Excel 2016+, Power Query is embedded and is accessible from the 'Data' tab. You can selected 'Queries and Connections' then double click on the query 'Filtered Result' to open the query editor. If you are on Excel 2010 or 2013, you need to download Power Query from https://www.microsoft.com/en-au/download/details.aspx?id=39379

I hope this helps. Please let me know if you have any questions

Thanks

Yury

[#5]

Hi Yury,

Thank you for the help. Have started studying the power query and i have started to understand some things which will help me in the future. Thank you for your valuable help.

Really appreciate the help.

Regards,

Aditya