Runtime Error '1004': Autofilter method of Range class failed --Trying to delete rows that do contain certain criteria

B

bpetty

New Member
Joined
Jun 16, 2015
Messages
2
Hello everyone,

I am a newbie that started teaching myself VBA YESTERDAY so that I can make my life using Excel considerably easier at my job (I like clicking one button and having my spreadsheet give me exactly what I need to report). Here is my issue:

I am exporting the responses from a Google form into Excel and then rearranging the data to send out as a report. The form works in such a way that it will take you down 1 of 2 pathways of questioning, depending on which medication is selected in one of the questions. I recorded a macro for one spreadsheet manipulation (for one group of medications), copied and pasted, removed the extraneous bits (active window scrolls, etc.) and am now finding that I have to do a little extra to this second sheet (for the other medication) to get it to only show data for ONE of the 2 possible options. I am doing all edits manually in VBA.

I've done some research on forums and found a bit of Autofilter code that I popped into the beginning of my macro (which I will paste below, sorry it's kind of long). I also had to delete some information due to confidentiality (items italicized).

I keep getting an error message that says "Runtime error:'1004': Autofilter method of Range class failed". I am just curious as to what I may be missing. I am completely aware, that even the autofilter method may be wrong, but as a said, I'm a newb.

What I am trying to do is filter column L (drug name) in my spreadsheet, and then delete all rows that do not contain the name of one certain drug in column L. Then, the rest of the macro works on the remaining data.

Any help would be greatly appreciated as I will have to generate these reports on a regular basis as the data continues to accumulate.

I am using MS Excel 2010 (Office Professional) on a Windows 7 OS.

Thank you in advance! :)

Macro is below:

Sub ScreeningLog_study name()
'
' ScreeningLog_study name Macro
'


'
Dim DelRows As Range
ActiveWorkbook.Worksheets("Form Responses 1").Range("L2:L100").AutoFilter Criteria1:="<>drug name as in table", VisibleDropDown:=False
Set DelRows = Range("_FilterDatabase").SpecialCells(xlCellTypeVisible)
ActiveWorkbook.Worksheets("Form Responses 1").AutoFilterMode = False
DelRows.EntireRow.Delete
Set DelRows = Nothing
ActiveWorkbook.Worksheets("Form Responses 1").Sort.SortFields.Add Key:=Range( _
"C2:C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Form Responses 1").Sort
.SetRange Range("A1:AH7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A:A,E:E,F:F,G:G,H:H,L:L,M:M,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG"). _
Select
Range("AG1").Activate
Selection.Delete Shift:=xlToLeft
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.RowHeight = 76.5
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:A").ColumnWidth = 14.86
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 9
Columns("D:D").ColumnWidth = 10.29
Columns("E:E").ColumnWidth = 17.14
Columns("F:F").ColumnWidth = 12.29
Columns("G:G").ColumnWidth = 16.71
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 42.86
Columns("J:J").ColumnWidth = 38.86
Columns("I:J").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:K").EntireColumn.AutoFit
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "SUBJECT SCREENING LOG - study name"
Range("A1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("A1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "Sponsor:"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Protocol:"
Range("B2").Select
ActiveCell.FormulaR1C1 = "sponsor name"
Range("B3").Select
ActiveCell.FormulaR1C1 = "number"
Range("A2:A3").Select
Selection.Font.Bold = True
Range("J2").Select
ActiveCell.FormulaR1C1 = "number"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
Range("B2:B3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2:J3").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J2").Select
ActiveCell.FormulaR1C1 = "Site ID: number"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("J3").Select
ActiveCell.FormulaR1C1 = "Investigator name: name"
With ActiveCell.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With ActiveCell.Characters(Start:=18, Length:=21).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Without scrolling through all the code, but your Sub Name is not allowed.
It can't have a space in it !!
Code: 
Sub ScreeningLog_study name()
SHOULD BE

Sub ScreeningLog_study_name()
 
Upvote 0
Hello Michael,

Thank you, that is just a stand in name for the posting. The actual name does not have any spaces. I just had to put some space fillers in when I posted bc the info is confidential.

Thanks,

bpetty


Michael M said:
Without scrolling through all the code, but your Sub Name is not allowed.
It can't have a space in it !!
Code: 
Sub ScreeningLog_study name()
SHOULD BE

Sub ScreeningLog_study_name()
Click to expand...
 
Upvote 0
Ok, I Don't have Excel at the moment, but I hae shortened the code by removing a heap of Select>Selections and =False lines.
Someone else may be able to respond to the rest
Code: 
Sub ScreeningLog_study_name()
Dim DelRows As Range
ActiveWorkbook.Worksheets("Form Responses 1").Range("L2:L100").AutoFilter Criteria1:="drug name as in table", VisibleDropDown:=False
Set DelRows = Range("_FilterDatabase").SpecialCells(xlCellTypeVisible)
ActiveWorkbook.Worksheets("Form Responses 1").AutoFilterMode = False
DelRows.EntireRow.Delete
Set DelRows = Nothing
ActiveWorkbook.Worksheets("Form Responses 1").Sort.SortFields.Add Key:=Range( _
"C2:C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Form Responses 1").Sort
.SetRange Range("A1:AH7")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A:A,E:E,F:F,G:G,H:H,L:L,M:M,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG"). _
Select
Range("AG1").Activate
Selection.Delete Shift:=xlToLeft
With Rows("1:1").Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
Rows("1:1").RowHeight = 76.5
Columns("A:A").ColumnWidth = 14.86
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").ColumnWidth = 9
Columns("D:D").ColumnWidth = 10.29
Columns("E:E").ColumnWidth = 17.14
Columns("F:F").ColumnWidth = 12.29
Columns("G:G").ColumnWidth = 16.71
Columns("H:H").EntireColumn.AutoFit
With Columns("I:J")
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
End With
Columns("I:K").EntireColumn.AutoFit
With Rows("1:1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
Cells.Borders(xlDiagonalDown).LineStyle = xlNone
Cells.Borders(xlDiagonalUp).LineStyle = xlNone
With Cells.Borders
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
Rows("1:1").Insert
Rows("2:2").Insert
Rows("3:3").Insert
Range("A1").Value = "SUBJECT SCREENING LOG - study name"
With Range("A1").Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Color = -16777216
End With
With Range("A1:J1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
Range("A2").Value = "Sponsor:"
Range("A3").Value = "Protocol:"
Range("B2").Value = "sponsor name"
Range("B3").Value = "number"
Range("A2:A3").Font.Bold = True
Range("J2").Value = "number"
Range("J3").Value = "Investigator name: name"
With Range("B2:B3")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
With Range("J2:J3")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
Range("J2").Value = "Site ID: number"
With Range("J2").Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Color = -16777216
End With
With Range("J2").Characters(Start:=10, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Color = -16777216
End With
Range("J3").Value = "Investigator name: name"
With Range("J3").Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Color = -16777216
End With
With Range("J3").Characters(Start:=18, Length:=21).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Color = -16777216
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
create a button that do actions
2 3
Replies
22
Views
840
kerm007
K
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
476
kevin9999
K
A
  • Solved
what am doing wrong?
Replies
5
Views
440
ageordieinneed
A
G
  • Solved
Does macro change font automatically when clsoing workbook and how do I get to do on mutiple sheets
Replies
3
Views
290
DanteAmor
DanteAmor
T
  • Question
Automation error / file crash
Replies
4
Views
510
Skyybot
Skyybot

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

Share this page

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
Back
Top