asked on

VB Autofilter Question

My apologies for my very rough VB code here but I need some help with turning the auto filter on. I have created this script to format a report and turn on the auto filter so the managers can quickly select their employees so they can perform an audit. I have pasted the code here, again my apologies for the newbie code here as it is a combination of macro recording and my coding.

Range("A:A,I:I,M:M").Select
Range("M1").Activate
Selection.NumberFormat = "mm/dd/yyyy h:mm:ss"
Rows("1:1").Select
ActiveCell.EntireRow.Insert
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date Requested"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "System Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Request #"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Requested By"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Approved By"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Ticket Number"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Approval Comments"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Ticket System"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Ticket Number"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Early Expire Reason"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Change Date/Time"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Change Status"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("L:L").Select
Selection.Cut Destination:=Columns("B:B")
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"(", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.NumberFormat = "General"
Range("G1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "Last Name"
Range("H1").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "First Name"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("I:K").Select
Selection.Cut Destination:=Columns("C:E")
Columns("I:K").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("I:K").EntireColumn.AutoFit
Columns("Q:Q").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("O:O").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Range("J1").Select
ActiveCell.FormulaR1C1 = "Request Reason"
Range("J2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Selection.autofilter
End Sub

My issue is with Range("A1").Select and Selection.autofilter. I would expect the autoflter to be placed in the row "A1", but when I run the code it shows up in row "A2". Any ideas as to why?

Phillip Burton

It shows up in A1 when I run it.

I would be surprised if it shows up in A2, since you have inserted an empty row there.

Maybe you should post the spreadsheet prior to running the code, so we can look at it.

Rrave26

ASKER

I just re ran it and it puts the filter in row A2. I have attached the file for troubleshooting.
DlyReleaseActivity-20140903.csv

Kimputer

I didn't see it either.

Otherwise, try this also:

combine the last range/selection to one line:

Range("A1").AutoFilter

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rrave26

ASKER

Thanks for the suggestion Kimputer but it still puts it in Row "A2".

Rrave26

ASKER

I see, thanks Phil. Let me fix that and rerun it.

Rob Henson

A couple of reduced sections of script:

For the section inserting the headers you can go through a loop with a variable for the column number:

For C = 1 to 19
Hdr = CHOOSE(C,"Date Requested", "Account Name", "System Name", "Request #", "Requested By", "Approved By", "Ticket Number", "Approval Comments",  "Ticket System", "Ticket Number", "Early Expire Reason", "Change Date/Time")
Cells(1,C) = Hdr
Next C

Open in new window

However you then change J1 to "Request Reason". Change "Ticket System" in the line above is this is required.

Also the section for Autofit can be shrunk to one line:

Columns("B:M").EntireColumn.AutoFit

Open in new window

Thanks
Rob H

Rob Henson

I missed the comment about column I not having a header and might have messed up on the copy and paste to get the headers listed but no doubt you see the gist. For column I just insert "{space}" in the relevant place.

Thanks
Rob H

Rrave26

ASKER

Phils catch resolved my issue.