find & delete + fill to end code?

  • Thread starter Thread starter direwolf
  • Start date Start date
D

direwolf

I have an excel spreadsheet which I want to add two things to a macro I
have.

This first thing is I want to search a single column and delete any
rows that have 0 (zero) in it.

The second thing I want to do is to fill a column with a word (the
column heading title) down to the last row. If the fill is done in
more or less completed rows then I get an error when I import the data
into another application.

So I suppose I need code to look for data in say, the first column and
stop when it finds a blank.
 
The first macro below, select any cell in the column you want to "scan" for zero, and run the macro
'----------------------------------------------------
Sub fine_zero_and_delete_row(
Dim tmp As Strin
Dim tmp2 As Singl
With Selectio
tmp = Application.Intersect(.CurrentRegion, ActiveSheet.Columns(.Column)).Addres
End Wit
With Range(tmp
For tmp2 = .Rows.Count To 1 Step -
If .Cells(tmp2).Value = 0 Then .Rows(tmp2).Delet
Nex
End Wit
End Su
'----------------------------------------------------------------

For the second task, I'm not sure whether you want to "replace" the non-blank cells in a columns, OR, you have a table with many columns and one of them is empty

If you want to "replace" non-blank cells, try the one below
Select any cell in the column you want to fill (replace non-blank cells), and run the macro
'----------------------------------------------------------------
Sub fill_to_bottom(
Dim tmp As Singl
Application.ScreenUpdating = Fals
tmp =
With Selection.Cells(1
Do Until .Offset(tmp, 0).Value = "
.Offset(tmp, 0).Value = "hello
tmp = tmp +
Loo
End Wit
End Su
'----------------------------------------------------------------

However, if you have a table and in one of the columns you want to fill the cells with a value. And you want the macro to stop at the LAST ROW of the table, try the following macro
Select any cell in the column to fill, and run the macro
'----------------------------------------------------------------
Sub fill_to_bottom2(
Dim tmp As String, cell As Objec
Application.ScreenUpdating = Fals
With Selectio
tmp = Application.Intersect(Columns(.Column), .CurrentRegion).Addres
End Wit
With Range(tmp
For Each cell In .Cell
cell.Value = "hello
Nex
End Wit
End Su
'----------------------------------------------------------------


----- direwolf wrote: ----

I have an excel spreadsheet which I want to add two things to a macro
have

This first thing is I want to search a single column and delete an
rows that have 0 (zero) in it

The second thing I want to do is to fill a column with a word (th
column heading title) down to the last row. If the fill is done i
more or less completed rows then I get an error when I import the dat
into another application.

So I suppose I need code to look for data in say, the first column an
stop when it finds a blank
 
Try this shortcut method.

Select the heading cell.
At the bottom right of the cell you will see the 'fill handle' - it i
a small 'x' in the corner.

Double click the fill handle.

It will fill down automatically, and stop at the first blank row.
This should also remove the zero's as they will be replaced by th
heading
 
I know how to do it all manually

The file I want to use this on is formated from its original form via a
macro. These last two steps are the ones I can't work out how to
include in the macro. I am currently doing them manually.

BTW the two different actions I want the macro to do are in different
columns. i.e. there is a totals column and a label column
 
Try these .

Sub ReplaceZeros()
' replace zeros
Columns("D:D").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False
End Sub

Sub CopyHeading()
' auto fill
Dim X As Range

Set X = ActiveSheet.[A1]
X.AutoFill Destination:=X.Resize(X.End(xlDown).Row, 1)
Range("A1:A10").Select
End Sub


Happy new year.
 
Thanks very much for the reply Kieran. :)

The first one replaces to zero in the cell with an empty cell. What I
need to do is delete the row that the cell is in.

The second one fills the column no problems. But I need it to stop
once it reaches the last row.
i.e. column A has the data in in

column B needs to have the label filled down to the row that the last
entry in column A is.

I hope I have explained it right.
 
I found this code and tried to get it to work, but I get and *Invalid o
Unqualified reference* error at .Find


Code
-------------------
Dim c As Range

Columns("B").Select
Do
Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End Wit
 
Dim c As Range

With Columns("B")
Do
Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
 
Hmmm

On closer examination this actually deletes the rows that have zero in
the figure anywhere

i.e. 108 or 500 or 20 will result in the row being deleted.

Is there a way to set it so it only deletes the row if the cell
contents is zero only and not just containing a zero?

Cheers
 
One way:


Change

Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)

to

Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
 
Cool Stuff, Works a treat

Thanks

:-)

One way:


Change

Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)

to

Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
Click to expand...
 
..find needs to be qualified by a range reference

try

Dim c As Range

With Columns("B")
Do
Set c = .Find("0", LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
 
Thanks Kieran, J.E. McGimpsey gave me this code which is similar to wha
you gave.

This one only deletes rows where the result = 0 where as I found you
code deletes any row containing 0 i.e. 100 or 308 etc


Dim c As Range

With Columns("B")
Do
Set c = .Find(0, LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End Wit
 
Your code is identical to what I posted 4 days ago. What are you trying to
say?

Find was qualified by a range reference then and you haven't changed it.
 
Kieran,
there was no offense (why try to personalize a legitimate technical
question) - in a newsgroup (which this is), the responses are threaded so a
conversation can be followed. The question was certainly apropro taken in
the context of the full discussion - which is the context from which it was
asked. I don't know what it looks like in ExcelForum, but if you can't see
the history of the discussion, you could continue to waste your time
duplicating answers that have already been given.

http://msnews.microsoft.com/Microsoft.Public.Excel.Programming
 
Back
Top