Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-07-28
5
43 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 50

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 50

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

856 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