How to remove blanks rows?

[santoshloka]

i need to delete 3 rows after 1 row
i need only x,y coordinates rest of them need to delete
is there any VBA which can help me to get it?
https://drive.google.com/open?id=0B8-usD7SI_4aN1pNQTVGb0hHWVU
please find the attachment below

 

[oldbrewer]

so delete rows 2,3,4, 6,7,8 etc ?

 

[NoSparks]

The way I interpret this you're wanting to delete more than just blank rows,
those the likes of 2fc7c are also to be deleted. If so, try this

Sub RemoveStuff()

Dim lr As Long, i As Long

Application.ScreenUpdating = False
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = lr To 1 Step -1
        If Left(.Cells(i, "A").Value, 2) <> "X=" Then
            .Cells(i, "A").EntireRow.Delete
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

If it is in fact just the blanks, give this a shot

Sub RemoveJustBlankLines()
    Sheets("Sheet1").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[C with no eyes]

Try this:

    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$228").AutoFilter Field:=1, Criteria1:="<>*x*", _
        Operator:=xlAnd
    Selection.Delete Shift:=xlUp

 

[hiker95]

i need only x,y coordinates rest of them need to delete

santoshloka,

Here is a macro solution for you to consider, that is based on your above quote, that does not do any looping thru the rows in your raw data Range("A1:A25").


Sample raw data flat text display:

2fc7c
X=252801.5687 Y=1965312.0195


2fc7d
X=252901.4375 Y=1965306.8999


2fc7e
X=253001.3064 Y=1965301.7802


2fc7f
X=253101.1752 Y=1965296.6605



2fc80
X=253201.0441 Y=1965291.5408


2fc81
X=253300.9130 Y=1965286.4212


And, after the macro in Range("A1:A6"):

X=252801.5687 Y=1965312.0195
X=252901.4375 Y=1965306.8999
X=253001.3064 Y=1965301.7802
X=253101.1752 Y=1965296.6605
X=253201.0441 Y=1965291.5408
X=253300.9130 Y=1965286.4212

Sub RemoveBlankRows_Plus()
' hiker95, 10/08/2017, ME1025990
Dim Addr As String
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(Left(@,1)<>""X"",""#N/A"",@)", "@", Addr))
On Error Resume Next
Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

 

[santoshloka]

is it possible to sepeate them 2columns with macro
X,Y

 

[Rick Rothstein]

is it possible to sepeate them 2columns with macro
X,Y

The following is hiker95's code (which is also the way I would have coded the answer to your original question) with one additional line of code (highlighted in red) to accomplish the split across columns you are now asking for...

[table="width: 500"]
[tr]
	[td]Sub RemoveBlankRows_Plus()
  ' hiker95, 10/08/2017, ME1025990
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(Left(@,1)<>""X"",""#N/A"",@)", "@", Addr))
  On Error Resume Next
  Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  [B][COLOR="#FF0000"]Range(Addr).TextToColumns , xlDelimited, , , False, False, False, True, False[/COLOR][/B]
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

Deleted

 

[santoshloka]

Thank you i got it

 

[hiker95]

is it possible to sepeate them 2columns with macro
X,Y

santoshloka,

Here is another macro solution for you to consider that is based on your latest request.

Sub RemoveBlankRows_Plus_V2()
' hiker95, 10/08/2017, ME1025990
Dim Addr As String
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim s
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(Left(@,1)<>""X"",""#N/A"",@)", "@", Addr))
On Error Resume Next
Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
a = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
ReDim o(1 To UBound(a, 1), 1 To 2)
For i = LBound(a, 1) To UBound(a, 1)
  s = Split(a(i, 1), "  ")
  j = j + 1: o(j, 1) = s(0): o(j, 2) = s(1)
Next i
Range("A1").Resize(UBound(o, 1), UBound(o, 2)) = o
Columns(1).Resize(, 2).AutoFit
End Sub