VBA Help

  • Thread starter Thread starter Robert Nguyen
  • Start date Start date
R

Robert Nguyen

I want to write a code that looks at my data and delete
all the unwanted lines. For example, say the first column
in my data contains the alphabet, I want to delete all the
lines that are not letters A, B, or C. Thanks in advance
for any help.
 
one way:

Public Sub DeleteDToZ()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With cell
If Not Left(.Text, 1) Like "[A-Ca-c]" Then
If delRange Is Nothing Then
Set delRange = .Cells
Else
Set delRange = Union(delRange, .Cells)
End If
End If
End With
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub
 
Thanks
-----Original Message-----
one way:

Public Sub DeleteDToZ()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With cell
If Not Left(.Text, 1) Like "[A-Ca-c]" Then
If delRange Is Nothing Then
Set delRange = .Cells
Else
Set delRange = Union(delRange, .Cells)
End If
End If
End With
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

I want to write a code that looks at my data and delete
all the unwanted lines. For example, say the first column
in my data contains the alphabet, I want to delete all the
lines that are not letters A, B, or C. Thanks in advance
for any help.
Click to expand...
.
Click to expand...
 
J.E. McGimpsey,

I really liked your example subroutine because it seems flexible
(accomodating row size), compact and efficient. I went through it yesterday
to understand it.

Just thought I would pass along my comments.

Regards,
Kevin


J.E. McGimpsey said:
one way:

Public Sub DeleteDToZ()
Dim cell As Range
Dim delRange As Range
For Each cell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With cell
If Not Left(.Text, 1) Like "[A-Ca-c]" Then
If delRange Is Nothing Then
Set delRange = .Cells
Else
Set delRange = Union(delRange, .Cells)
End If
End If
End With
Next cell
If Not delRange Is Nothing Then delRange.EntireRow.Delete
End Sub

Robert Nguyen said:
I want to write a code that looks at my data and delete
all the unwanted lines. For example, say the first column
in my data contains the alphabet, I want to delete all the
lines that are not letters A, B, or C. Thanks in advance
for any help.
Click to expand...
Click to expand...
 
Back
Top