VBA removing heading in spreadsheet

[JadeRogers]

Hi, needing some assistance on a code i currently have.
When i leave my code as follows it works as wanted but removes header rows. when changing to "A2:A" it hides all rows.

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
With ws.Range("A1:A" & lastRow)
.AutoFilter Field:=1, Criteria1:="0"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Value = ""
.AutoFilter
End With

Im assuming it may be placed incorrectly :| Full code as follows:

' SAPMonday Macro
'

'
Cells.Select
Selection.ColumnWidth = 8.43
Columns("A:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("U:U").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2", Range("B2").End(xlDown))
Range("B2", Range("B2").End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
With ws.Range("A2:A" & lastRow)
.AutoFilter Field:=1, Criteria1:="0"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Value = ""
.AutoFilter
End With
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

 

[rlv01]

You might try something like this.

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim FilterRange As Range, DataRange As Range, ClearRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.AutoFilterMode = False
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set FilterRange = ws.Range("A1:A" & LastRow) 'includes header row
    With FilterRange
        Set DataRange = .Offset(1).Resize(.Rows.Count - 1) 'excludes header row
        .AutoFilter Field:=1, Criteria1:="0"
        On Error Resume Next
        Set ClearRange = DataRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not ClearRange Is Nothing Then
            ClearRange.Value = ""
        End If
    End With
    ws.AutoFilterMode = False

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[JadeRogers]

You might try something like this.

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim FilterRange As Range, DataRange As Range, ClearRange As Range
  
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.AutoFilterMode = False
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set FilterRange = ws.Range("A1:A" & LastRow) 'includes header row
    With FilterRange
        Set DataRange = .Offset(1).Resize(.Rows.Count - 1) 'excludes header row
        .AutoFilter Field:=1, Criteria1:="0"
        On Error Resume Next
        Set ClearRange = DataRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not ClearRange Is Nothing Then
            ClearRange.Value = ""
        End If
    End With
    ws.AutoFilterMode = False

Unfortunately Run-time error '1004' occurs here:

        Set DataRange = .Offset(1).Resize(.Rows.Count - 1) 'excludes header row

 

[rlv01]

Unfortunately Run-time error '1004' occurs here:

tmpabc.xlsm
	A	B	C	D	E	F	G	H
1	Data Col 1	Data Col 2	Data Col 3	Data Col 4	Data Col 5	Data Col 6	Data Col 7	Data Col 8
2
3
4
5
6
7
8
9
10
Sheet1

If your sheet1 has no data, that is the error you will get. Please use XL2BB to post some example data so I can see how you have organized it.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com