Excel VBA / Data Sorting / Maniputaltion

  • Thread starter Thread starter Abdul
  • Start date Start date
A

Abdul

i have a long list, which i imported from other data base.
The problem is:
1. In each cell, besides data (data is in alphabates) there are som
numeric characters which i dont want .
How can i get rid of all those unwanted numbers from data, from all th
list?

2. The data is in every second row, ie. one row is empty.
How can i Delete those empty/blank rows from the list
 
Abdul

Here is a quick solution it works on the selction.

Sub removeNums()
Dim c, p
Dim l As Integer, i As Integer
For Each c In Selection
l = Len(c)
For i = l To 1 Step -1
p = Mid(c, i, 1)
If Asc(p) > 47 And Asc(p) < 58 Then
c.Value = Left(c, Len(c) - 1)
Else: c = Trim(c)
End If
Next i
Next c
End Sub


Regards
Peter
 
Removing empty row can be done with

Sub removeRows()
Dim i As Long, nr As Long
nr = Application.WorksheetFunction.CountA(Range("A:A"))
For i = nr To 1 Step -1
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub

Regards
Peter
 
Back
Top