vba code doesn't work

I found an example of vba code in a book "Using Excel 2003". It doesn't work
and locks the entire application up. The code is:
Sub removenull()
Application.ScreenUpdating = False
Do Until ActiveCell.Value = " "
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub


It is supposed to remove 0 from rows and delete rows.

What is wrong with it?

Do Until ActiveCell.Value = ""
Should have no space between the quotes
--
- K Dales


"lschuh" wrote:

> I found an example of vba code in a book "Using Excel 2003". It doesn't work
> and locks the entire application up. The code is:
> Sub removenull()
> Application.ScreenUpdating = False
> Do Until ActiveCell.Value = " "
> If ActiveCell.Value = 0 Then
> ActiveCell.EntireRow.Delete
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Loop
> End Sub
>
>
> It is supposed to remove 0 from rows and delete rows.
>
> What is wrong with it?

you might like this better
Sub deletezero()
For i = ActiveCell.End(xlDown).Row To 1 Step -1
If Cells(i, 1) = 0 Then Rows(i).Delete
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"lschuh" <[email protected]> wrote in message
news:[email protected]...
> I found an example of vba code in a book "Using Excel 2003". It doesn't
work
> and locks the entire application up. The code is:
> Sub removenull()
> Application.ScreenUpdating = False
> Do Until ActiveCell.Value = " "
> If ActiveCell.Value = 0 Then
> ActiveCell.EntireRow.Delete
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Loop
> End Sub
>
>
> It is supposed to remove 0 from rows and delete rows.
>
> What is wrong with it?

I will try this. Thanks.

"Don Guillett" wrote:

> you might like this better
> Sub deletezero()
> For i = ActiveCell.End(xlDown).Row To 1 Step -1
> If Cells(i, 1) = 0 Then Rows(i).Delete
> Next
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I found an example of vba code in a book "Using Excel 2003". It doesn't
> work
> > and locks the entire application up. The code is:
> > Sub removenull()
> > Application.ScreenUpdating = False
> > Do Until ActiveCell.Value = " "
> > If ActiveCell.Value = 0 Then
> > ActiveCell.EntireRow.Delete
> > Else
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop
> > End Sub
> >
> >
> > It is supposed to remove 0 from rows and delete rows.
> >
> > What is wrong with it?
>
>
>

Much appreciation. It worked fine.

"K Dales" wrote:

> Do Until ActiveCell.Value = ""
> Should have no space between the quotes
> --
> - K Dales
>
>
> "lschuh" wrote:
>
> > I found an example of vba code in a book "Using Excel 2003". It doesn't work
> > and locks the entire application up. The code is:
> > Sub removenull()
> > Application.ScreenUpdating = False
> > Do Until ActiveCell.Value = " "
> > If ActiveCell.Value = 0 Then
> > ActiveCell.EntireRow.Delete
> > Else
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop
> > End Sub
> >
> >
> > It is supposed to remove 0 from rows and delete rows.
> >
> > What is wrong with it?

In the "Do..." line of your macro, do you mean to say "Until it is blank"?
I ask because that line does not say that. It says "Until it has a single
space character in it". "Blank" is "". You have " ".
I don't know what you mean for your macro to do, but be aware that the
"Value" of a cell is a numerical zero if the cell has a 0 character in it or
if it is blank. Therefore the statement:
If ActiveCell.Value = 0
is True if the cell is blank and it is True if the cell has a 0 character in
it.
If you want to test if the entry in that cell is a 0 character, then put the
0 character in quotes, as "0".
HTH Otto
"lschuh" <[email protected]> wrote in message
news:[email protected]...
>I found an example of vba code in a book "Using Excel 2003". It doesn't
>work
> and locks the entire application up. The code is:
> Sub removenull()
> Application.ScreenUpdating = False
> Do Until ActiveCell.Value = " "
> If ActiveCell.Value = 0 Then
> ActiveCell.EntireRow.Delete
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Loop
> End Sub
>
>
> It is supposed to remove 0 from rows and delete rows.
>
> What is wrong with it?

In going through my worksheet I have used a transferspreadsheet from
MSAccess. The data within the query that transfers has several 0 in it so I
don't want to remove those. However, there are several rows of blank data at
the end of the spreadsheet that are null records. Can I do some kind of
modification of this code to look for rows with empty records?

"K Dales" wrote:

> Do Until ActiveCell.Value = ""
> Should have no space between the quotes
> --
> - K Dales
>
>
> "lschuh" wrote:
>
> > I found an example of vba code in a book "Using Excel 2003". It doesn't work
> > and locks the entire application up. The code is:
> > Sub removenull()
> > Application.ScreenUpdating = False
> > Do Until ActiveCell.Value = " "
> > If ActiveCell.Value = 0 Then
> > ActiveCell.EntireRow.Delete
> > Else
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop
> > End Sub
> >
> >
> > It is supposed to remove 0 from rows and delete rows.
> >
> > What is wrong with it?

If you are using this code

> Sub deletezero()
> For i = ActiveCell.End(xlDown).Row To 1 Step -1
> If Cells(i, 1) = 0 Then Rows(i).Delete
> Next
> End Sub

You might enter an additional elseif (providing you are looking for
numerical entries)
(of course you can combine them into a single if: If condition1 or
condition 2 then...)
Elseif worksheetfunction.sum(Rows(1))= 0 then

or
Elseif worksheetfunction.Count(Rows(1)) = 0 then


--
steveB

Remove "AYN" from email to respond
"lschuh" <[email protected]> wrote in message
news:[email protected]...
> In going through my worksheet I have used a transferspreadsheet from
> MSAccess. The data within the query that transfers has several 0 in it so
> I
> don't want to remove those. However, there are several rows of blank data
> at
> the end of the spreadsheet that are null records. Can I do some kind of
> modification of this code to look for rows with empty records?
>
> "K Dales" wrote:
>
>> Do Until ActiveCell.Value = ""
>> Should have no space between the quotes
>> --
>> - K Dales
>>
>>
>> "lschuh" wrote:
>>
>> > I found an example of vba code in a book "Using Excel 2003". It
>> > doesn't work
>> > and locks the entire application up. The code is:
>> > Sub removenull()
>> > Application.ScreenUpdating = False
>> > Do Until ActiveCell.Value = " "
>> > If ActiveCell.Value = 0 Then
>> > ActiveCell.EntireRow.Delete
>> > Else
>> > ActiveCell.Offset(1, 0).Select
>> > End If
>> > Loop
>> > End Sub
>> >
>> >
>> > It is supposed to remove 0 from rows and delete rows.
>> >
>> > What is wrong with it?

sort of on the same thread
The data that is transferring from Access does not always have the same
amount of rows. The data contains hearing charts that are listed by years.
Depending on the seniority dates each employee can have different beginning
start dates. What I tried to do was add extra space on the spreadsheet to
accomodate those employees who had more time with the company. The hearing
tests were not required until 1979. I created the spreadsheet to account for
at least 26 rows. If an employee started in say 1998 and a query is ran then
the rows will be deleted. However on the next query if the employee has been
with the company since 1985 those extra rows have been deleted from the
previous macro and now the spreadsheet only shows the number of the last
query. In other words the spreadsheet is not resetting itself to accomodate
the employees who have been here longer. Is there some way I can write the
macro to maybe leave the number of rows to a common value (at least 26 lines
of data) and clear not delete the 0 or data that is residing within the rows?
This probably does not make any sense.

"lschuh" wrote:

> I found an example of vba code in a book "Using Excel 2003". It doesn't work
> and locks the entire application up. The code is:
> Sub removenull()
> Application.ScreenUpdating = False
> Do Until ActiveCell.Value = " "
> If ActiveCell.Value = 0 Then
> ActiveCell.EntireRow.Delete
> Else
> ActiveCell.Offset(1, 0).Select
> End If
> Loop
> End Sub
>
>
> It is supposed to remove 0 from rows and delete rows.
>
> What is wrong with it?

Sounds like you are moving right along - Great!

As you may be seeing, there are many different ways to "get there
from here"...

The trick is finding the one that works best, doesn't error out, and meets
ALL your needs...

keep on Exceling

--
steveB

Remove "AYN" from email to respond
"lschuh" <[email protected]> wrote in message
news:[email protected]...
> that works great, will try some more variations. thanks
>
> "STEVE BELL" wrote:
>
>> If you are using this code
>>
>> > Sub deletezero()
>> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
>> > If Cells(i, 1) = 0 Then Rows(i).Delete
>> > Next
>> > End Sub
>>
>> You might enter an additional elseif (providing you are looking for
>> numerical entries)
>> (of course you can combine them into a single if: If condition1 or
>> condition 2 then...)
>> Elseif worksheetfunction.sum(Rows(1))= 0 then
>>
>> or
>> Elseif worksheetfunction.Count(Rows(1)) = 0 then
>>
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "lschuh" <[email protected]> wrote in message
>> news:[email protected]...
>> > In going through my worksheet I have used a transferspreadsheet from
>> > MSAccess. The data within the query that transfers has several 0 in it
>> > so
>> > I
>> > don't want to remove those. However, there are several rows of blank
>> > data
>> > at
>> > the end of the spreadsheet that are null records. Can I do some kind
>> > of
>> > modification of this code to look for rows with empty records?
>> >
>> > "K Dales" wrote:
>> >
>> >> Do Until ActiveCell.Value = ""
>> >> Should have no space between the quotes
>> >> --
>> >> - K Dales
>> >>
>> >>
>> >> "lschuh" wrote:
>> >>
>> >> > I found an example of vba code in a book "Using Excel 2003". It
>> >> > doesn't work
>> >> > and locks the entire application up. The code is:
>> >> > Sub removenull()
>> >> > Application.ScreenUpdating = False
>> >> > Do Until ActiveCell.Value = " "
>> >> > If ActiveCell.Value = 0 Then
>> >> > ActiveCell.EntireRow.Delete
>> >> > Else
>> >> > ActiveCell.Offset(1, 0).Select
>> >> > End If
>> >> > Loop
>> >> > End Sub
>> >> >
>> >> >
>> >> > It is supposed to remove 0 from rows and delete rows.
>> >> >
>> >> > What is wrong with it?
>>
>>
>>

the syntax has been fixed, I will look at the rest of your code. Thanks for
responding

"Otto Moehrbach" wrote:

> In the "Do..." line of your macro, do you mean to say "Until it is blank"?
> I ask because that line does not say that. It says "Until it has a single
> space character in it". "Blank" is "". You have " ".
> I don't know what you mean for your macro to do, but be aware that the
> "Value" of a cell is a numerical zero if the cell has a 0 character in it or
> if it is blank. Therefore the statement:
> If ActiveCell.Value = 0
> is True if the cell is blank and it is True if the cell has a 0 character in
> it.
> If you want to test if the entry in that cell is a 0 character, then put the
> 0 character in quotes, as "0".
> HTH Otto
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> >I found an example of vba code in a book "Using Excel 2003". It doesn't
> >work
> > and locks the entire application up. The code is:
> > Sub removenull()
> > Application.ScreenUpdating = False
> > Do Until ActiveCell.Value = " "
> > If ActiveCell.Value = 0 Then
> > ActiveCell.EntireRow.Delete
> > Else
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop
> > End Sub
> >
> >
> > It is supposed to remove 0 from rows and delete rows.
> >
> > What is wrong with it?
>
>
>

that works great, will try some more variations. thanks

"STEVE BELL" wrote:

> If you are using this code
>
> > Sub deletezero()
> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > If Cells(i, 1) = 0 Then Rows(i).Delete
> > Next
> > End Sub
>
> You might enter an additional elseif (providing you are looking for
> numerical entries)
> (of course you can combine them into a single if: If condition1 or
> condition 2 then...)
> Elseif worksheetfunction.sum(Rows(1))= 0 then
>
> or
> Elseif worksheetfunction.Count(Rows(1)) = 0 then
>
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > In going through my worksheet I have used a transferspreadsheet from
> > MSAccess. The data within the query that transfers has several 0 in it so
> > I
> > don't want to remove those. However, there are several rows of blank data
> > at
> > the end of the spreadsheet that are null records. Can I do some kind of
> > modification of this code to look for rows with empty records?
> >
> > "K Dales" wrote:
> >
> >> Do Until ActiveCell.Value = ""
> >> Should have no space between the quotes
> >> --
> >> - K Dales
> >>
> >>
> >> "lschuh" wrote:
> >>
> >> > I found an example of vba code in a book "Using Excel 2003". It
> >> > doesn't work
> >> > and locks the entire application up. The code is:
> >> > Sub removenull()
> >> > Application.ScreenUpdating = False
> >> > Do Until ActiveCell.Value = " "
> >> > If ActiveCell.Value = 0 Then
> >> > ActiveCell.EntireRow.Delete
> >> > Else
> >> > ActiveCell.Offset(1, 0).Select
> >> > End If
> >> > Loop
> >> > End Sub
> >> >
> >> >
> >> > It is supposed to remove 0 from rows and delete rows.
> >> >
> >> > What is wrong with it?
>
>
>

This gets rid of the rows where column B is blank. You can change the column
designation to whatever column you want. It is more efficient than the
looping code...

Sub RemoveBlanks()
Dim rngToSearch As Range
Dim wks As Worksheet

Set wks = ActiveSheet
'The 2 refers to column B. Change it to whatever...
Set rngToSearch = wks.Columns(2).SpecialCells(xlCellTypeBlanks)

If Not rngToSearch Is Nothing Then rngToSearch.EntireRow.Delete

End Sub
--
HTH...

Jim Thomlinson


"lschuh" wrote:

> In going through my worksheet I have used a transferspreadsheet from
> MSAccess. The data within the query that transfers has several 0 in it so I
> don't want to remove those. However, there are several rows of blank data at
> the end of the spreadsheet that are null records. Can I do some kind of
> modification of this code to look for rows with empty records?
>
> "K Dales" wrote:
>
> > Do Until ActiveCell.Value = ""
> > Should have no space between the quotes
> > --
> > - K Dales
> >
> >
> > "lschuh" wrote:
> >
> > > I found an example of vba code in a book "Using Excel 2003". It doesn't work
> > > and locks the entire application up. The code is:
> > > Sub removenull()
> > > Application.ScreenUpdating = False
> > > Do Until ActiveCell.Value = " "
> > > If ActiveCell.Value = 0 Then
> > > ActiveCell.EntireRow.Delete
> > > Else
> > > ActiveCell.Offset(1, 0).Select
> > > End If
> > > Loop
> > > End Sub
> > >
> > >
> > > It is supposed to remove 0 from rows and delete rows.
> > >
> > > What is wrong with it?

I put your code into my macro and placed it in the personal.xls
It wouldn't run without declaring the variable i
I deleted the option explicit and it runs.
My question do you know how to declare the variable?
Should I delete the macro from the personal.xls and put it into the
worksheet I am opening.
Will the macro run with other open worksheets. It seems to be running on
its own.
What is the best way to go?


"Don Guillett" wrote:

> you might like this better
> Sub deletezero()
> For i = ActiveCell.End(xlDown).Row To 1 Step -1
> If Cells(i, 1) = 0 Then Rows(i).Delete
> Next
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I found an example of vba code in a book "Using Excel 2003". It doesn't
> work
> > and locks the entire application up. The code is:
> > Sub removenull()
> > Application.ScreenUpdating = False
> > Do Until ActiveCell.Value = " "
> > If ActiveCell.Value = 0 Then
> > ActiveCell.EntireRow.Delete
> > Else
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop
> > End Sub
> >
> >
> > It is supposed to remove 0 from rows and delete rows.
> >
> > What is wrong with it?
>
>
>

I tried to run this code and can not get the line
set rngtosearch=wks.columns(2).specialcells(xlcelltypeblanks)
to run. Keep getting an error.

"Jim Thomlinson" wrote:

> This gets rid of the rows where column B is blank. You can change the column
> designation to whatever column you want. It is more efficient than the
> looping code...
>
> Sub RemoveBlanks()
> Dim rngToSearch As Range
> Dim wks As Worksheet
>
> Set wks = ActiveSheet
> 'The 2 refers to column B. Change it to whatever...
> Set rngToSearch = wks.Columns(2).SpecialCells(xlCellTypeBlanks)
>
> If Not rngToSearch Is Nothing Then rngToSearch.EntireRow.Delete
>
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "lschuh" wrote:
>
> > In going through my worksheet I have used a transferspreadsheet from
> > MSAccess. The data within the query that transfers has several 0 in it so I
> > don't want to remove those. However, there are several rows of blank data at
> > the end of the spreadsheet that are null records. Can I do some kind of
> > modification of this code to look for rows with empty records?
> >
> > "K Dales" wrote:
> >
> > > Do Until ActiveCell.Value = ""
> > > Should have no space between the quotes
> > > --
> > > - K Dales
> > >
> > >
> > > "lschuh" wrote:
> > >
> > > > I found an example of vba code in a book "Using Excel 2003". It doesn't work
> > > > and locks the entire application up. The code is:
> > > > Sub removenull()
> > > > Application.ScreenUpdating = False
> > > > Do Until ActiveCell.Value = " "
> > > > If ActiveCell.Value = 0 Then
> > > > ActiveCell.EntireRow.Delete
> > > > Else
> > > > ActiveCell.Offset(1, 0).Select
> > > > End If
> > > > Loop
> > > > End Sub
> > > >
> > > >
> > > > It is supposed to remove 0 from rows and delete rows.
> > > >
> > > > What is wrong with it?

You get an error when there are no blank cells in the usedrange of column 2.
Perhaps they just look blank but are not.

--
Regards,
Tom Ogilvy


"lschuh" <[email protected]> wrote in message
news:[email protected]...
> I tried to run this code and can not get the line
> set rngtosearch=wks.columns(2).specialcells(xlcelltypeblanks)
> to run. Keep getting an error.
>
> "Jim Thomlinson" wrote:
>
> > This gets rid of the rows where column B is blank. You can change the
column
> > designation to whatever column you want. It is more efficient than the
> > looping code...
> >
> > Sub RemoveBlanks()
> > Dim rngToSearch As Range
> > Dim wks As Worksheet
> >
> > Set wks = ActiveSheet
> > 'The 2 refers to column B. Change it to whatever...
> > Set rngToSearch = wks.Columns(2).SpecialCells(xlCellTypeBlanks)
> >
> > If Not rngToSearch Is Nothing Then rngToSearch.EntireRow.Delete
> >
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "lschuh" wrote:
> >
> > > In going through my worksheet I have used a transferspreadsheet from
> > > MSAccess. The data within the query that transfers has several 0 in
it so I
> > > don't want to remove those. However, there are several rows of blank
data at
> > > the end of the spreadsheet that are null records. Can I do some kind
of
> > > modification of this code to look for rows with empty records?
> > >
> > > "K Dales" wrote:
> > >
> > > > Do Until ActiveCell.Value = ""
> > > > Should have no space between the quotes
> > > > --
> > > > - K Dales
> > > >
> > > >
> > > > "lschuh" wrote:
> > > >
> > > > > I found an example of vba code in a book "Using Excel 2003". It
doesn't work
> > > > > and locks the entire application up. The code is:
> > > > > Sub removenull()
> > > > > Application.ScreenUpdating = False
> > > > > Do Until ActiveCell.Value = " "
> > > > > If ActiveCell.Value = 0 Then
> > > > > ActiveCell.EntireRow.Delete
> > > > > Else
> > > > > ActiveCell.Offset(1, 0).Select
> > > > > End If
> > > > > Loop
> > > > > End Sub
> > > > >
> > > > >
> > > > > It is supposed to remove 0 from rows and delete rows.
> > > > >
> > > > > What is wrong with it?

> Sub deletezero()
Dim i as Long
> For i = ActiveCell.End(xlDown).Row To 1 Step -1
> If Cells(i, 1) = 0 Then Rows(i).Delete
> Next
> End Sub

--
Regards,
Tom Ogilvy

"lschuh" <[email protected]> wrote in message
news:[email protected]...
> I put your code into my macro and placed it in the personal.xls
> It wouldn't run without declaring the variable i
> I deleted the option explicit and it runs.
> My question do you know how to declare the variable?
> Should I delete the macro from the personal.xls and put it into the
> worksheet I am opening.
> Will the macro run with other open worksheets. It seems to be running on
> its own.
> What is the best way to go?
>
>
> "Don Guillett" wrote:
>
> > you might like this better
> > Sub deletezero()
> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > If Cells(i, 1) = 0 Then Rows(i).Delete
> > Next
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "lschuh" <[email protected]> wrote in message
> > news:[email protected]...
> > > I found an example of vba code in a book "Using Excel 2003". It
doesn't
> > work
> > > and locks the entire application up. The code is:
> > > Sub removenull()
> > > Application.ScreenUpdating = False
> > > Do Until ActiveCell.Value = " "
> > > If ActiveCell.Value = 0 Then
> > > ActiveCell.EntireRow.Delete
> > > Else
> > > ActiveCell.Offset(1, 0).Select
> > > End If
> > > Loop
> > > End Sub
> > >
> > >
> > > It is supposed to remove 0 from rows and delete rows.
> > >
> > > What is wrong with it?
> >
> >
> >

I thought I was. What I need to do is be able to clear contents that has
cell references to another worksheet. The data is coming in from Access.
Not all data has the same number of rows. I would like to start with a
worksheet that stays formatted the same. After the data is transferred if I
have rows I do not have anything other than cell references which would
return 0 then I would like to delete those rows so the worksheet looks clean.
I would like to worksheet to open the same way it did before I removed the
0. Is it possible to keep the formatting constant the same each time you
open the worksheet?

"STEVE BELL" wrote:

> Sounds like you are moving right along - Great!
>
> As you may be seeing, there are many different ways to "get there
> from here"...
>
> The trick is finding the one that works best, doesn't error out, and meets
> ALL your needs...
>
> keep on Exceling
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > that works great, will try some more variations. thanks
> >
> > "STEVE BELL" wrote:
> >
> >> If you are using this code
> >>
> >> > Sub deletezero()
> >> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> >> > If Cells(i, 1) = 0 Then Rows(i).Delete
> >> > Next
> >> > End Sub
> >>
> >> You might enter an additional elseif (providing you are looking for
> >> numerical entries)
> >> (of course you can combine them into a single if: If condition1 or
> >> condition 2 then...)
> >> Elseif worksheetfunction.sum(Rows(1))= 0 then
> >>
> >> or
> >> Elseif worksheetfunction.Count(Rows(1)) = 0 then
> >>
> >>
> >> --
> >> steveB
> >>
> >> Remove "AYN" from email to respond
> >> "lschuh" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > In going through my worksheet I have used a transferspreadsheet from
> >> > MSAccess. The data within the query that transfers has several 0 in it
> >> > so
> >> > I
> >> > don't want to remove those. However, there are several rows of blank
> >> > data
> >> > at
> >> > the end of the spreadsheet that are null records. Can I do some kind
> >> > of
> >> > modification of this code to look for rows with empty records?
> >> >
> >> > "K Dales" wrote:
> >> >
> >> >> Do Until ActiveCell.Value = ""
> >> >> Should have no space between the quotes
> >> >> --
> >> >> - K Dales
> >> >>
> >> >>
> >> >> "lschuh" wrote:
> >> >>
> >> >> > I found an example of vba code in a book "Using Excel 2003". It
> >> >> > doesn't work
> >> >> > and locks the entire application up. The code is:
> >> >> > Sub removenull()
> >> >> > Application.ScreenUpdating = False
> >> >> > Do Until ActiveCell.Value = " "
> >> >> > If ActiveCell.Value = 0 Then
> >> >> > ActiveCell.EntireRow.Delete
> >> >> > Else
> >> >> > ActiveCell.Offset(1, 0).Select
> >> >> > End If
> >> >> > Loop
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > It is supposed to remove 0 from rows and delete rows.
> >> >> >
> >> >> > What is wrong with it?
> >>
> >>
> >>
>
>
>

thanks.

"Tom Ogilvy" wrote:

> You get an error when there are no blank cells in the usedrange of column 2.
> Perhaps they just look blank but are not.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I tried to run this code and can not get the line
> > set rngtosearch=wks.columns(2).specialcells(xlcelltypeblanks)
> > to run. Keep getting an error.
> >
> > "Jim Thomlinson" wrote:
> >
> > > This gets rid of the rows where column B is blank. You can change the
> column
> > > designation to whatever column you want. It is more efficient than the
> > > looping code...
> > >
> > > Sub RemoveBlanks()
> > > Dim rngToSearch As Range
> > > Dim wks As Worksheet
> > >
> > > Set wks = ActiveSheet
> > > 'The 2 refers to column B. Change it to whatever...
> > > Set rngToSearch = wks.Columns(2).SpecialCells(xlCellTypeBlanks)
> > >
> > > If Not rngToSearch Is Nothing Then rngToSearch.EntireRow.Delete
> > >
> > > End Sub
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "lschuh" wrote:
> > >
> > > > In going through my worksheet I have used a transferspreadsheet from
> > > > MSAccess. The data within the query that transfers has several 0 in
> it so I
> > > > don't want to remove those. However, there are several rows of blank
> data at
> > > > the end of the spreadsheet that are null records. Can I do some kind
> of
> > > > modification of this code to look for rows with empty records?
> > > >
> > > > "K Dales" wrote:
> > > >
> > > > > Do Until ActiveCell.Value = ""
> > > > > Should have no space between the quotes
> > > > > --
> > > > > - K Dales
> > > > >
> > > > >
> > > > > "lschuh" wrote:
> > > > >
> > > > > > I found an example of vba code in a book "Using Excel 2003". It
> doesn't work
> > > > > > and locks the entire application up. The code is:
> > > > > > Sub removenull()
> > > > > > Application.ScreenUpdating = False
> > > > > > Do Until ActiveCell.Value = " "
> > > > > > If ActiveCell.Value = 0 Then
> > > > > > ActiveCell.EntireRow.Delete
> > > > > > Else
> > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > End If
> > > > > > Loop
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > It is supposed to remove 0 from rows and delete rows.
> > > > > >
> > > > > > What is wrong with it?
>
>
>

thank you. now if I attach it to the worksheet I want it to run in will it
1) run upon open
2) manually need to activate it.

"Tom Ogilvy" wrote:

> > Sub deletezero()
> Dim i as Long
> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > If Cells(i, 1) = 0 Then Rows(i).Delete
> > Next
> > End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I put your code into my macro and placed it in the personal.xls
> > It wouldn't run without declaring the variable i
> > I deleted the option explicit and it runs.
> > My question do you know how to declare the variable?
> > Should I delete the macro from the personal.xls and put it into the
> > worksheet I am opening.
> > Will the macro run with other open worksheets. It seems to be running on
> > its own.
> > What is the best way to go?
> >
> >
> > "Don Guillett" wrote:
> >
> > > you might like this better
> > > Sub deletezero()
> > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > Next
> > > End Sub
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "lschuh" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I found an example of vba code in a book "Using Excel 2003". It
> doesn't
> > > work
> > > > and locks the entire application up. The code is:
> > > > Sub removenull()
> > > > Application.ScreenUpdating = False
> > > > Do Until ActiveCell.Value = " "
> > > > If ActiveCell.Value = 0 Then
> > > > ActiveCell.EntireRow.Delete
> > > > Else
> > > > ActiveCell.Offset(1, 0).Select
> > > > End If
> > > > Loop
> > > > End Sub
> > > >
> > > >
> > > > It is supposed to remove 0 from rows and delete rows.
> > > >
> > > > What is wrong with it?
> > >
> > >
> > >
>
>
>

I put that in and my worksheet just started blinking and I had to break out
of the application. It ran before when I removed the option explicit and
placed it in the personal.xls I put it in the worksheet I wanted it to
run in and it spun out.

"Tom Ogilvy" wrote:

> > Sub deletezero()
> Dim i as Long
> > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > If Cells(i, 1) = 0 Then Rows(i).Delete
> > Next
> > End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I put your code into my macro and placed it in the personal.xls
> > It wouldn't run without declaring the variable i
> > I deleted the option explicit and it runs.
> > My question do you know how to declare the variable?
> > Should I delete the macro from the personal.xls and put it into the
> > worksheet I am opening.
> > Will the macro run with other open worksheets. It seems to be running on
> > its own.
> > What is the best way to go?
> >
> >
> > "Don Guillett" wrote:
> >
> > > you might like this better
> > > Sub deletezero()
> > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > Next
> > > End Sub
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "lschuh" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I found an example of vba code in a book "Using Excel 2003". It
> doesn't
> > > work
> > > > and locks the entire application up. The code is:
> > > > Sub removenull()
> > > > Application.ScreenUpdating = False
> > > > Do Until ActiveCell.Value = " "
> > > > If ActiveCell.Value = 0 Then
> > > > ActiveCell.EntireRow.Delete
> > > > Else
> > > > ActiveCell.Offset(1, 0).Select
> > > > End If
> > > > Loop
> > > > End Sub
> > > >
> > > >
> > > > It is supposed to remove 0 from rows and delete rows.
> > > >
> > > > What is wrong with it?
> > >
> > >
> > >
>
>
>

First, it should go in a general module. If it ran without any action on
your part, then you must have placed it inside an event procedure or called
it from an event procedure.

the only macros that run without be told to run are macros fired by events.

If you want it to work in a specific column, then you might alter it to
refer to that column

Sub deletezero()
Dim i as Long
For i = Worksheets("sheet1").Range("B1") _
.End(xlDown).Row To 1 Step -1
If Worksheets("Sheet1").Cells(i, 1) = 0 Then _
Worksheets("Sheet1").Rows(i).Delete
Next
End Sub

As an example.

If you want to fire it when the workbook is opened, then call it from the
workbook_Open event found in the Thisworkbook.module.

--
Regards,
Tom Ogilvy



"lschuh" <[email protected]> wrote in message
news:[email protected]...
> I put that in and my worksheet just started blinking and I had to break
out
> of the application. It ran before when I removed the option explicit and
> placed it in the personal.xls I put it in the worksheet I wanted it
to
> run in and it spun out.
>
> "Tom Ogilvy" wrote:
>
> > > Sub deletezero()
> > Dim i as Long
> > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > Next
> > > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "lschuh" <[email protected]> wrote in message
> > news:[email protected]...
> > > I put your code into my macro and placed it in the personal.xls
> > > It wouldn't run without declaring the variable i
> > > I deleted the option explicit and it runs.
> > > My question do you know how to declare the variable?
> > > Should I delete the macro from the personal.xls and put it into the
> > > worksheet I am opening.
> > > Will the macro run with other open worksheets. It seems to be running
on
> > > its own.
> > > What is the best way to go?
> > >
> > >
> > > "Don Guillett" wrote:
> > >
> > > > you might like this better
> > > > Sub deletezero()
> > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > Next
> > > > End Sub
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > [email protected]
> > > > "lschuh" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I found an example of vba code in a book "Using Excel 2003". It
> > doesn't
> > > > work
> > > > > and locks the entire application up. The code is:
> > > > > Sub removenull()
> > > > > Application.ScreenUpdating = False
> > > > > Do Until ActiveCell.Value = " "
> > > > > If ActiveCell.Value = 0 Then
> > > > > ActiveCell.EntireRow.Delete
> > > > > Else
> > > > > ActiveCell.Offset(1, 0).Select
> > > > > End If
> > > > > Loop
> > > > > End Sub
> > > > >
> > > > >
> > > > > It is supposed to remove 0 from rows and delete rows.
> > > > >
> > > > > What is wrong with it?
> > > >
> > > >
> > > >
> >
> >
> >

I am going to try that. I am not that familiar with excel coding sometimes I
am not that familiar with any vba (sort of kidding). I need to look at what
I am asking this program to do.

"Tom Ogilvy" wrote:

> First, it should go in a general module. If it ran without any action on
> your part, then you must have placed it inside an event procedure or called
> it from an event procedure.
>
> the only macros that run without be told to run are macros fired by events.
>
> If you want it to work in a specific column, then you might alter it to
> refer to that column
>
> Sub deletezero()
> Dim i as Long
> For i = Worksheets("sheet1").Range("B1") _
> .End(xlDown).Row To 1 Step -1
> If Worksheets("Sheet1").Cells(i, 1) = 0 Then _
> Worksheets("Sheet1").Rows(i).Delete
> Next
> End Sub
>
> As an example.
>
> If you want to fire it when the workbook is opened, then call it from the
> workbook_Open event found in the Thisworkbook.module.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I put that in and my worksheet just started blinking and I had to break
> out
> > of the application. It ran before when I removed the option explicit and
> > placed it in the personal.xls I put it in the worksheet I wanted it
> to
> > run in and it spun out.
> >
> > "Tom Ogilvy" wrote:
> >
> > > > Sub deletezero()
> > > Dim i as Long
> > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > Next
> > > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "lschuh" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I put your code into my macro and placed it in the personal.xls
> > > > It wouldn't run without declaring the variable i
> > > > I deleted the option explicit and it runs.
> > > > My question do you know how to declare the variable?
> > > > Should I delete the macro from the personal.xls and put it into the
> > > > worksheet I am opening.
> > > > Will the macro run with other open worksheets. It seems to be running
> on
> > > > its own.
> > > > What is the best way to go?
> > > >
> > > >
> > > > "Don Guillett" wrote:
> > > >
> > > > > you might like this better
> > > > > Sub deletezero()
> > > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > > Next
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > [email protected]
> > > > > "lschuh" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I found an example of vba code in a book "Using Excel 2003". It
> > > doesn't
> > > > > work
> > > > > > and locks the entire application up. The code is:
> > > > > > Sub removenull()
> > > > > > Application.ScreenUpdating = False
> > > > > > Do Until ActiveCell.Value = " "
> > > > > > If ActiveCell.Value = 0 Then
> > > > > > ActiveCell.EntireRow.Delete
> > > > > > Else
> > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > End If
> > > > > > Loop
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > It is supposed to remove 0 from rows and delete rows.
> > > > > >
> > > > > > What is wrong with it?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

what if I want to work within a specific range. The range being a1:q32
would it then scan that work area and make the changes the code is telling
it to?

"Tom Ogilvy" wrote:

> First, it should go in a general module. If it ran without any action on
> your part, then you must have placed it inside an event procedure or called
> it from an event procedure.
>
> the only macros that run without be told to run are macros fired by events.
>
> If you want it to work in a specific column, then you might alter it to
> refer to that column
>
> Sub deletezero()
> Dim i as Long
> For i = Worksheets("sheet1").Range("B1") _
> .End(xlDown).Row To 1 Step -1
> If Worksheets("Sheet1").Cells(i, 1) = 0 Then _
> Worksheets("Sheet1").Rows(i).Delete
> Next
> End Sub
>
> As an example.
>
> If you want to fire it when the workbook is opened, then call it from the
> workbook_Open event found in the Thisworkbook.module.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > I put that in and my worksheet just started blinking and I had to break
> out
> > of the application. It ran before when I removed the option explicit and
> > placed it in the personal.xls I put it in the worksheet I wanted it
> to
> > run in and it spun out.
> >
> > "Tom Ogilvy" wrote:
> >
> > > > Sub deletezero()
> > > Dim i as Long
> > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > Next
> > > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "lschuh" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I put your code into my macro and placed it in the personal.xls
> > > > It wouldn't run without declaring the variable i
> > > > I deleted the option explicit and it runs.
> > > > My question do you know how to declare the variable?
> > > > Should I delete the macro from the personal.xls and put it into the
> > > > worksheet I am opening.
> > > > Will the macro run with other open worksheets. It seems to be running
> on
> > > > its own.
> > > > What is the best way to go?
> > > >
> > > >
> > > > "Don Guillett" wrote:
> > > >
> > > > > you might like this better
> > > > > Sub deletezero()
> > > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > > Next
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > [email protected]
> > > > > "lschuh" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I found an example of vba code in a book "Using Excel 2003". It
> > > doesn't
> > > > > work
> > > > > > and locks the entire application up. The code is:
> > > > > > Sub removenull()
> > > > > > Application.ScreenUpdating = False
> > > > > > Do Until ActiveCell.Value = " "
> > > > > > If ActiveCell.Value = 0 Then
> > > > > > ActiveCell.EntireRow.Delete
> > > > > > Else
> > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > End If
> > > > > > Loop
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > It is supposed to remove 0 from rows and delete rows.
> > > > > >
> > > > > > What is wrong with it?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Sub deletezero()
Dim i as Long
Dim cell as Range, dim rng as Range, rng1 as Range
For each cell in Worksheets("sheet1").Range("A1:Q32")
if not isempty(cell) and cell.value = 0 then
if rng is nothing then
set rng1 = rng
else
set rng1 = union(rng,rng1)
end if
end if
Next
if not rng1 is nothing then
rng1.Entirerow.Delete
End if
End Sub

If only a specific column in that range

Sub deletezero()
Dim i as Long
Dim cell as Range, dim rng as Range, rng1 as Range
For each cell in Worksheets("sheet1").Range("C1:C32")
if not isempty(cell) and cell.Value = 0 then
if rng is nothing then
set rng1 = rng
else
set rng1 = union(rng,rng1)
end if
end if
Next
if not rng1 is nothing then
rng1.Entirerow.Delete
End if
End Sub

--
Regards,
Tom Ogilvy



"lschuh" <[email protected]> wrote in message
news:[email protected]...
> what if I want to work within a specific range. The range being a1:q32
> would it then scan that work area and make the changes the code is telling
> it to?
>
> "Tom Ogilvy" wrote:
>
> > First, it should go in a general module. If it ran without any action
on
> > your part, then you must have placed it inside an event procedure or
called
> > it from an event procedure.
> >
> > the only macros that run without be told to run are macros fired by
events.
> >
> > If you want it to work in a specific column, then you might alter it to
> > refer to that column
> >
> > Sub deletezero()
> > Dim i as Long
> > For i = Worksheets("sheet1").Range("B1") _
> > .End(xlDown).Row To 1 Step -1
> > If Worksheets("Sheet1").Cells(i, 1) = 0 Then _
> > Worksheets("Sheet1").Rows(i).Delete
> > Next
> > End Sub
> >
> > As an example.
> >
> > If you want to fire it when the workbook is opened, then call it from
the
> > workbook_Open event found in the Thisworkbook.module.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "lschuh" <[email protected]> wrote in message
> > news:[email protected]...
> > > I put that in and my worksheet just started blinking and I had to
break
> > out
> > > of the application. It ran before when I removed the option explicit
and
> > > placed it in the personal.xls I put it in the worksheet I wanted
it
> > to
> > > run in and it spun out.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > > Sub deletezero()
> > > > Dim i as Long
> > > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > > Next
> > > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "lschuh" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I put your code into my macro and placed it in the personal.xls
> > > > > It wouldn't run without declaring the variable i
> > > > > I deleted the option explicit and it runs.
> > > > > My question do you know how to declare the variable?
> > > > > Should I delete the macro from the personal.xls and put it into
the
> > > > > worksheet I am opening.
> > > > > Will the macro run with other open worksheets. It seems to be
running
> > on
> > > > > its own.
> > > > > What is the best way to go?
> > > > >
> > > > >
> > > > > "Don Guillett" wrote:
> > > > >
> > > > > > you might like this better
> > > > > > Sub deletezero()
> > > > > > For i = ActiveCell.End(xlDown).Row To 1 Step -1
> > > > > > If Cells(i, 1) = 0 Then Rows(i).Delete
> > > > > > Next
> > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > > Don Guillett
> > > > > > SalesAid Software
> > > > > > [email protected]
> > > > > > "lschuh" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > I found an example of vba code in a book "Using Excel 2003".
It
> > > > doesn't
> > > > > > work
> > > > > > > and locks the entire application up. The code is:
> > > > > > > Sub removenull()
> > > > > > > Application.ScreenUpdating = False
> > > > > > > Do Until ActiveCell.Value = " "
> > > > > > > If ActiveCell.Value = 0 Then
> > > > > > > ActiveCell.EntireRow.Delete
> > > > > > > Else
> > > > > > > ActiveCell.Offset(1, 0).Select
> > > > > > > End If
> > > > > > > Loop
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > It is supposed to remove 0 from rows and delete rows.
> > > > > > >
> > > > > > > What is wrong with it?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >