Solved

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

Posted on 2016-07-28
5
38 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
See if this works, I haven't had chance to test because I'm just leaving for work
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

743 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

19 Experts available now in Live!

Get 1:1 Help Now