Programming in excel is new to me.
I want to delete rows of imported data based on comparing
the data in column M. Column M is where the batch numbers
are located. I have made several attempts to write a code
that will delete rows that do not contain numbers, or even
just the blank rows.
Please help.
I need to also understand exactly what is wrong, so I
won't make the same mistakes next time.
The following is the latest attempt:

Sub CheckBatch()
..ActiveSheet
If Field13 = (xlCellTypeBlanks) Then EntireRow.Delete
End Sub
'delete rows without a batch number

Sub remove_blanks()
Dim i As Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Range("A" & i).Value = "" Then
Range("A" & i).EntireRow.Delete (xlShiftUp)
end if
Next
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi CLK,

Try:

Sub sTester()

Dim rng As Range

On Error Resume Next
Set rng = Columns("C").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub

---
Regards,
Norman



"clk" wrote in message
...
Programming in excel is new to me.
I want to delete rows of imported data based on comparing
the data in column M. Column M is where the batch numbers
are located. I have made several attempts to write a code
that will delete rows that do not contain numbers, or even
just the blank rows.
Please help.
I need to also understand exactly what is wrong, so I
won't make the same mistakes next time.
The following is the latest attempt:

Sub CheckBatch()
.ActiveSheet
If Field13 = (xlCellTypeBlanks) Then EntireRow.Delete
End Sub
'delete rows without a batch number

Hi CLK,

For your purposes, change:

Set rng = Columns("C").SpecialCells(xlBlanks)

to

Set rng = Columns("M").SpecialCells(xlBlanks)


I used column C in my test and forgot to change it back!

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi CLK,

Try:

Sub sTester()

Dim rng As Range

On Error Resume Next
Set rng = Columns("C").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub

---
Regards,
Norman



"clk" wrote in message
...
Programming in excel is new to me.
I want to delete rows of imported data based on comparing
the data in column M. Column M is where the batch numbers
are located. I have made several attempts to write a code
that will delete rows that do not contain numbers, or even
just the blank rows.
Please help.
I need to also understand exactly what is wrong, so I
won't make the same mistakes next time.
The following is the latest attempt:

Sub CheckBatch()
.ActiveSheet
If Field13 = (xlCellTypeBlanks) Then EntireRow.Delete
End Sub
'delete rows without a batch number

I know it's been a couple of days, so I hope you're still
checking. I copied and pasted the formula into a macro
and then changed the column reference to C and changed the
title. It reads as follows:

Sub ledger1()

Dim rng As Range

On Error Resume Next
Set rng = Columns("M").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub


However, I'm still getting error messages in the module
before I can even try to run the program.


-----Original Message-----
Hi CLK,

For your purposes, change:

Set rng = Columns("C").SpecialCells(xlBlanks)

to

Set rng = Columns("M").SpecialCells(xlBlanks)


I used column C in my test and forgot to change it back!

---
Regards,
Norman


"Norman Jones" wrote in
message
...
Hi CLK,

Try:

Sub sTester()

Dim rng As Range

On Error Resume Next
Set rng = Columns("C").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub

---
Regards,
Norman



"clk" wrote in
message
...
Programming in excel is new to me.
I want to delete rows of imported data based on
comparing
the data in column M. Column M is where the batch
numbers
are located. I have made several attempts to write a
code
that will delete rows that do not contain numbers, or
even
just the blank rows.
Please help.
I need to also understand exactly what is wrong, so I
won't make the same mistakes next time.
The following is the latest attempt:

Sub CheckBatch()
.ActiveSheet
If Field13 = (xlCellTypeBlanks) Then
EntireRow.Delete
End Sub
'delete rows without a batch number





.

Hi CLK,

However, I'm still getting error messages in the module
before I can even try to run the program.


What are the error message are you getting?
What is highlighted?

Do you have other code in the module?


---
Regards,
Norman



"CLK" wrote in message
...
I know it's been a couple of days, so I hope you're still
checking. I copied and pasted the formula into a macro
and then changed the column reference to C and changed the
title. It reads as follows:

Sub ledger1()

Dim rng As Range

On Error Resume Next
Set rng = Columns("M").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub


However, I'm still getting error messages in the module
before I can even try to run the program.


-----Original Message-----
Hi CLK,

For your purposes, change:

Set rng = Columns("C").SpecialCells(xlBlanks)

to

Set rng = Columns("M").SpecialCells(xlBlanks)


I used column C in my test and forgot to change it back!

---
Regards,
Norman


"Norman Jones" wrote in
message
...
Hi CLK,

Try:

Sub sTester()

Dim rng As Range

On Error Resume Next
Set rng = Columns("C").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
rng.EntireRow.Delete
End If

End Sub

---
Regards,
Norman



"clk" wrote in
message
...
Programming in excel is new to me.
I want to delete rows of imported data based on
comparing
the data in column M. Column M is where the batch
numbers
are located. I have made several attempts to write a
code
that will delete rows that do not contain numbers, or
even
just the blank rows.
Please help.
I need to also understand exactly what is wrong, so I
won't make the same mistakes next time.
The following is the latest attempt:

Sub CheckBatch()
.ActiveSheet
If Field13 = (xlCellTypeBlanks) Then
EntireRow.Delete
End Sub
'delete rows without a batch number





.

Well, you can write a code that will go through the entire page in one
fell swoop...this assumes two things, first, that the L column has data
wether M does or does not, and that L column will return empty when
there is no more rows to test

Sub Test1()
Dim Msgme 'Creates variable named Msgme
Dim Sturge 'Creates variable named Sturge
Sturge = 1 'Set this to the row number of the first row that contains
data (1 = Row 1)

Range("L" & Sturge).Select 'Selects starting Cell

LabelLoop:
If ActiveCell.Value = Empty Then 'If L column cell contains nothing
then do this
GoTo endme
End If
ActiveCell.Offset(0, 1).Activate 'This pushes over to M column
If ActiveCell.Value = Empty Then 'If M ain't got no batch number
then do this
Selection.EntireRow.Delete 'Delete it!!!

GoTo bypass
End If
ActiveCell.Offset(1, -1).Activate
GoTo LabelLoop

bypass:
ActiveCell.Offset(0, -1).Activate


GoTo LabelLoop


endme:
Msgme = MsgBox("I am Done!!", vbOKOnly, "BatchFinder")
End Sub


That should do it.