Solved

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

Posted on 2016-07-28
5
40 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 49

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 17

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 49

Accepted Solution

by:
Rgonzo1971 earned 500 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 17

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now