Solved

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

Posted on 2016-07-28
5
42 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 18

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 18

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

770 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