PDA

View Full Version : Solved: Macro to delete the row



sindhuja
01-19-2008, 11:09 PM
Hi All,

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

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
01-20-2008, 12:00 AM
And if its an alphanumerical string?

Bob Phillips
01-20-2008, 03:08 AM
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

sindhuja
01-20-2008, 06:11 AM
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



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
01-20-2008, 06:18 AM
Back to fron.

Change



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


to



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

sindhuja
01-20-2008, 06:57 AM
Hi Xld...

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

Have a nice day:friends:

Regards,
Sindhuja

Aussiebear
01-20-2008, 01:44 PM
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?



Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim sh As Worksheet



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?



Set sh = ActiveSheet

With sh

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

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

Bob Phillips
01-20-2008, 03:50 PM
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
01-20-2008, 04:32 PM
Grasshopper very pleased with his effort Master.

:bow:

sindhuja
01-20-2008, 08:41 PM
Hi All,

Thanks for the informations !!

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

Regards,
Sindhuja

Aussiebear
01-21-2008, 01:25 AM
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".

:hi: