Solved: Macro to delete the row

[sindhuja]

Hi All,

I am new to VBA... can you please helpme out in resolving my issue....

My concern is

i have a excel sheet with numerous data.... i want to check column A and delete the entire row if any cell in colum A contains value other than numbers..

i want this to be done using macros as data will be greater than 25000 rows....

Thanks for your help in advance..!

Regards,
Sindhuja

[Aussiebear]

And if its an alphanumerical string?

[Bob Phillips]

[vba]

Public Sub DeleteAFterr()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set sh = ActiveSheet

With sh

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row


iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

If IsNumeric(.Cells(i, TEST_COLUMN).Value) Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(.Rows(i), rng)
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

Set sh = Nothing
End Sub
[/vba]

[sindhuja]

[vba]

Public Sub DeleteAFterr()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set sh = ActiveSheet

With sh

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row


iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

If IsNumeric(.Cells(i, TEST_COLUMN).Value) Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(.Rows(i), rng)
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

Set sh = Nothing
End Sub
[/vba]

Hi,

Thanks for all your help.

The code works fine in deleting the rows which has numbers as the value in column A.

But my requirement is i want the rows to be deleted with the values as alphabets or alpha numerics in column A....


Regards,
Sindhuja

p

[Bob Phillips]

Back to fron.

Change

[vba]

If IsNumeric(.Cells(i, TEST_COLUMN).Value) Then
[/vba]

to

[vba]

If Not IsNumeric(.Cells(i, TEST_COLUMN).Value) Then
[/vba]

[sindhuja]

Hi Xld...

Its perfect !!!!
Thanks a lot for your timely help...

Have a nice day

Regards,
Sindhuja

[Aussiebear]

Bob, 2 questions in relation to your code if I may. In the initial section where you declare the variables, I noticed iLastRow is not declared. Is this because "Option Explicit" was not used or is it not necessary?

[vba]
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim sh As Worksheet

[/vba]

Later on, within the Set sh = Active Sheet section you indicate that LastRow and iLastRow are the same thing. Could we get away with not indicating the Lastrow bit?

[vba]
Set sh = ActiveSheet

With sh

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

[/vba]

[Bob Phillips]

Ted,

That was a typo on my part. They should both be LastRow, I was meaning use Lastrow throughout. I changed my template code today and messed up by the looks. Sindhuja probably got away with it because he doesn't use Option Explicit.

Thanks for the heads up, I will correct my template.

[Aussiebear]

Grasshopper very pleased with his effort Master.

[sindhuja]

Hi All,

Thanks for the informations !!

It really helped me out and learnt a lot...

Regards,
Sindhuja

[Aussiebear]

G'day sindhuja,

If you are happy with the response then please use the Thread Tools options at the top of the page to mark this thread "solved".