Can anyone help me create a macro that will delete cells based on the criteria above. And run a loop that will check only cells with data in them so it doesn't try and go through every existing row. The cells I want checked is "F".
Thank you to anyone willing to take a look at this.
-Justair
Can you attach a workbook that contains sample data for us to work with...so we can tailor possible solutions for you?
I forgot to add one in my original post, it should be attached now.
Thank you,
Hi Justair,
try the code below - on your sample it worked fine.
Please click the * below if this helps
Sub test() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False On Error GoTo earlyexit If Not Range("A1").Value = "" Then Rows("1:1").Insert Cells.AutoFilter ActiveSheet.Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).AutoFilter Field:=6, Criteria1:="<>Complete", Operator:=xlAnd, Criteria2:="<>Finished" Range("A1", ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete earlyexit: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub
Please click the * below if this helps
Awesome thank you, but is there a way that The first column can be excluded. I forgot about my headers.
Replace this
withIf Not Range("A1").Value = "" Then Rows("1:1").Insert
This would just not add the Insert row. But avoiding deletion of header in Jasper code, I need Jasper help.Range("A1").Select
Last edited by shiva_reshs; 09-19-2013 at 03:57 PM.
Keep the Forum clean :
1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
That still deletes the first column, i even tried:
Range("A2").Select
Usually headers are in the first ROW, not in the first COLUMN, but anyway.
This will keep the data in the first column:
and this will keep the first rowSub keep1stcol() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False On Error GoTo earlyexit If Not Range("A1").Value = "" Then Rows("1:1").Insert Cells.AutoFilter ActiveSheet.Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).AutoFilter Field:=6, Criteria1:="<>Complete", Operator:=xlAnd, Criteria2:="<>Finished" Range("B1", ActiveCell.SpecialCells(xlLastCell)).Delete shift:=xlToLeft Rows(1).EntireRow.Delete earlyexit: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub
Please click the * below if this helpsSub keep1strow() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False On Error GoTo earlyexit Range("A2", ActiveCell.SpecialCells(xlLastCell)).AutoFilter ActiveSheet.Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).AutoFilter Field:=6, Criteria1:="<>Complete", Operator:=xlAnd, Criteria2:="<>Finished" Range("A2", ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete earlyexit: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub
Perfect, thank you JasperD!!
Jasper, The code seems to take a very long time to run. Do you if there is a way to improve the speed?
I could not imagine a loop could be faster than a filter, but try the attached.
AB33, lighting fast!! Only problem is it deletes my headers in Row 1
And re-formats my columns?Originally Posted by Justair07
And re-formats my columns?
Change this line
INTO.Range("A1").Resize(n, UBound(X, 2)) = result
Please explain what do you mean by re-formats my columns?.Range("A2").Resize(n, UBound(X, 2)) = result
How on earth can a loop be quicker than a filter... ?
I'm not sure but it's almost instant compared to 2 minutes. I'm not smart enough to understand why.
Awesome thanks for the help, works great!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Justair07 
Register To Reply
Bookmarks