?
Solved

Delete all rows of the current worksheet that start with an alphabetical character

Posted on 2016-07-28
5
Medium Priority
?
54 Views
Last Modified: 2016-07-29
Dear Experts

I got a worksheet with rows that start with ...
... an alpabetical character and ...
... rows that start with a numerical character

Using a VBA code, I would like to delete all the rows that start with an alphabetical character and leave the other ones alone.

Help is much appreciated. Thank you very much in advance.

delete_text_rows.xls

Regards, Andreas

I have attached a sample file for your convenience.
0
Comment
Question by:AndreasHermle
  • 2
  • 2
5 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 41734197
Hi,

pls try

Sub Macro()

For Idx = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If IsNumeric(Left(Range("A" & Idx), 1)) = False Then
        Range("A" & Idx).EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41734203
See if this works, I haven't had chance to test because I'm just leaving for work
0
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41734204
Or quicker

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Rng.Offset(, 1).Formula = "=IF(ISNUMBER(--LEFT(A1,1)),1,NA())"
Rng.Offset(, 1).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("b:b").ClearContents

Open in new window

0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41734219
My code seems to have disappeared
Option Explicit
Sub removeRows()
Dim c As Range, MyRange As Range
Dim r As Long
  For Each c In ActiveSheet.Range("A1").CurrentRegion.Columns(1)
            If Not IsNumeric(Left(Trim(c.Text), 1)) Then
            If r = 0 Then
            Set MyRange = c
            r = 1
            Else: Set MyRange = Union(MyRange, c)
            End If
            End If
        Next c
        MyRange.Delete
End Sub
0
 

Author Closing Comment

by:AndreasHermle
ID: 41734282
Hi Rgonzo,

both codes work just fine, although as you stated this one is faster. Thank you very much for your great and professional help.

Roy: regrettably your code deletes everything, nevertheless, thank you very much for your support.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question