Solved: Deleting Rows or AutoFilter

[vzachin]

hi,

I have a file that I copied & pasted from a WORD doc. I need to do a text to column but there are "Header" rows that I need to delete. These "Headers" most often are defined names. The data that I want to text to column always has leading spaces.
I tried to do an AutoFilter but I can't get it to work correctly. I cannot sort and delete the headers because I need to retain the original format of the data.
Any suggestions?

thanks
zach

[acw]

Hi

Perhaps run a macro across the data to remove those "Header" rows (the first character in the row will not be a space), then either do the text to columns manually, or record the structure and have it run as part of the macro????


Tony

[vzachin]

hi Tony,

how do i test for the first character not to be a space? i already have the macro to do the text to columns. i just can't figure out how to code testing for first character.

thanks
zach

[vzachin]

this is what i came up with. it works

[VBA]
With Sheets("Sheet1")
Range("B1").FormulaR1C1 = "=IF(LEFT(RC[-1],1)<>"" "",""1"","""")"
Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row).FillDown
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:B1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="1"
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("1:" & iLastRow).SpecialCells(xlCellTypeVisible).Delete
End With
End Sub
[/VBA]

thanks again
zach