vba code doesn't work

G

Guest

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?
 
D

Don Guillett

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
 
O

Otto Moehrbach

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
 
G

Guest

I will try this. Thanks.

Don Guillett said:
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
Click to expand...
 
G

Guest

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?
 
S

STEVE BELL

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
Click to expand...

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
 
G

Guest

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
 
S

STEVE BELL

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
 
G

Guest

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.
 
G

Guest

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?
 
G

Guest

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.
 
T

Tom Ogilvy

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

Guest

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA code deleting everything not just rows with specified values. Please help. 4
Simple Macro? 5
Help with Loop 2
why is my loop so slow? 1
preset range 3
PLEASE help ... 3
Speed it up? 4
VB Slows to Crawl when Deleting Rows 2

Top