Nov 15 2017
- last edited on
Jul 25 2018
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.
Nov 15 2017 07:43 AM
please find attached my solution using VBA. The macro text is:
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
bDeleteRow = True
If bDeleteRow Then
Rows(r + iFirstDataRow - 1).Delete
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.
Nov 20 2017 10:08 PM
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.
Nov 26 2017 12:21 AMSolution
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
Nov 26 2017 10:42 PM
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.