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?

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 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" wrote in message
...
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?

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" wrote in message
...
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?

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" wrote in message
...
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" wrote in message
...
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 tried to run this code and can not get the line
set rngtosearch=wks.columns(2).specialcells(xlcelltype blanks)
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" wrote in message
...
I tried to run this code and can not get the line
set rngtosearch=wks.columns(2).specialcells(xlcelltype blanks)
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 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

"lschuh" wrote in message
...
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

"lschuh" wrote in message
...
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

"lschuh" wrote in message
...
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" wrote in message
...
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

"lschuh" wrote in message
...
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" wrote in message
...
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

"lschuh" wrote in message
...
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" wrote in message
...
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

"lschuh" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"lschuh" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?