Need Help with Programming-Syntax/Compile Errors

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:uriayVd%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.

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" <[email protected]> wrote in
message
>news:uriayVd%[email protected]...
>> 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" <[email protected]> wrote in
message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in
> message
>>news:uriayVd%[email protected]...
>>> 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" <[email protected]> wrote in
> message
>>> news:[email protected]...
>>>> 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
>>>>
>>>
>>>
>>
>>
>>.
>>