New to VBA. Getting a Compile Error. Please help.

[trduckett4]

This code is designed to take a spreadsheet of raw data, omit several columns, and reformat what remains. I welcome any and all critiques of the code as I am new to VBA and know nothing. The key problem is at the end of the code on the line indicated with the emoticon. This is the spot where the "Compile Error: Invalid Qualifier" comes up. I'm trying to apply the formatting to Columns B and F, but I only want it to go as far as the last row of date. The last row of data will vary from one sheet to the next.

When the error is triggered, the debugger highlights the word "count" as indicated in bold below.

Thanks for the help and your patience in helping a newbie along in his journey.


Sub Macro2()
'Macro2 Macro
Union(Range("A:A"), Range("F:F"), Range("K:Q"), Range("S:V")).Delete
Range("A1").Select
ActiveCell.FormulaR1C1 = "FIRST"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LAST"
Range("C1").Select
ActiveCell.FormulaR1C1 = "G"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PHONE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ADDRESS"
Range("F1").Select
ActiveCell.FormulaR1C1 = "CITY"
Range("G1").Select
ActiveCell.FormulaR1C1 = "STATE"
Range("H1").Select
ActiveCell.FormulaR1C1 = "ZIP"
Range("I1").Select
ActiveCell.FormulaR1C1 = "MONTH"
Range("J1").Select
ActiveCell.FormulaR1C1 = "YEAR"
Columns("e:h").Insert Shift:=xlToRight
Columns("A:B").ColumnWidth = 12
Columns("C:C").ColumnWidth = 2
Columns("D:d").ColumnWidth = 13
Columns("e:e").ColumnWidth = 0.38
Columns("F:F").ColumnWidth = 5
Columns("G:G").ColumnWidth = 11
Columns("H:H").ColumnWidth = 0.38
Columns("I:N").ColumnWidth = 14
:dunnoUnion(Range("B:B"), Range("F:F")).Rows.Count.End(xlUp).Row
Range("B1").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
End Sub

[westconn1]

as .end(xlup) really only works correctly for a single column (first column in a range) your code may not return the correct result
if you want the last cell regardless of which column, you may have to test each column, then get the greatest

try like

With Union(Range("a:A"), Range("B:B"), Range("F:F"))
For Each col In .Columns
lr = .Cells(.Rows.Count, col.Column).End(xlUp).Row
If lr > lastrow Then lastrow = lr
Next
End With

[Paul_Hossler]

Not sure about the rest, but in



Union(Range("B:B"), Range("F:F")).Rows.Count.End(xlUp).Row



the .Count returns the number of rows so it's a Long at that point

You can't add the .End to it since .End needs a Range

Paul

[jolivanes]

You could replace all the selecting, which generally is frowned upon, by something like this.

Dim headers()
Dim i As Long
headers = Array("First", "Last", "G", "Phone", "Address", "City", "State", "Zip", "Month", "Year")
For i = 0 To UBound(headers)
Cells(1, i + 1).Value = headers(i)
Next i
and for the problem line maybe use this instead

lrB = Cells(Rows.Count,2).End(xlUp).Row
lrF = Cells(Rows.Count,6).End(xlUp).Row

[sassora]

Hi

I think it's as Paul says, "end" needs a range. I've assumed you want the last row of B and F.

Note that you don't need to select cells to change their values in VBA (see below). Jolivanes used an array to populate the headings in a neater, fancier way.

Sub Macro2()

'Last row of B and F
LastRow_BF = Application.Max(Range("B" & Rows.Count).End(xlUp).Row, Range("F" & Rows.Count).End(xlUp).Row)

Union(Range("A:A"), Range("F:F"), Range("K:Q"), Range("S:V")).Delete
Range("A1").Value = "FIRST"
Range("B1").Value = "LAST"
Range("C1").Value = "G"
Range("D1").Value = "PHONE"
Range("E1").Value = "ADDRESS"
Range("F1").Value = "CITY"
Range("G1").Value = "STATE"
Range("H1").Value = "ZIP"
Range("I1").Value = "MONTH"
Range("J1").Value = "YEAR"

Columns("e:h").Insert Shift:=xlToRight
Columns("A:B").ColumnWidth = 12
Columns("C:C").ColumnWidth = 2
Columns("D:d").ColumnWidth = 13
Columns("e:e").ColumnWidth = 0.38
Columns("F:F").ColumnWidth = 5
Columns("G:G").ColumnWidth = 11
Columns("H:H").ColumnWidth = 0.38
Columns("I:N").ColumnWidth = 14

'Color B1 - most of the properties specified before are defaults
Range("B1").Interior.ThemeColor = xlThemeColorAccent5
Range("B1").Interior.TintAndShade = 0.599993896298105
End Sub