Macros

[foolio]

Okay yet another problem....

I have three rows (one empty one on the right) of data like this

5
23 2
4
4
3 6

1 8

what i want to do is put zero's into everything that is blank. (note
want to do this in a macro). i found a way of doing it....which i
mainly in the macro i create a row that contains all "0" and copy an
paste special (transpose) onto the two rows..... now this works but i
ends up doing this....


5 0 0
23 2 0
0 4 0
4 0 0
3 6 0
0 0 0
1 8 0
0 0 0
0 0 0
0 0 0 <----extra zeroes
0 0 0
0 0 0

i dont want the extra zeroes on the end....and no i cant jsut make th
initial row of zeroes that im copying the same length of the row i
pasting to because i am using this macro in multiple documents and th
row length are always changing.

anyways any help would be awesome

thx, fooli

 

[Dave Peterson]

That first row with a 4 in it. It got shoved over to the left. Is that a
posting problem?

If your data actually has that 4 in the leftmost column, how did you know to
move it to the right?

I'm guessing it was a newsgroup post problem.

Record a macro when you do this:

Select your range (A1:C99?)
Edit|goto|special|Blanks
type 0
hit ctrl-Enter

and all the blanks will be filled with 0s.

I got something like this when I recorded those actions:

Option Explicit
Sub Macro1()
Range("A1:C10").Select
Range("C10").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "0"
End Sub

You could clean it up by:

Option Explicit
Sub Macro1()
With ActiveSheet.Range("A1:C10")
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0
End With
End Sub

The "on error" stuff stops your code from stopping if there are no blanks in
that range.

 

[foolio]

thats almost exactly what i want....my only other question is: is ther
a way to do it so i dont have to input a range....in other words i
there a way to make it automatically find the last row in the table an
use that as its range ?

and yes the 4 was a posting problem....the first 4 should have been i
the middle row

 

[Dave Peterson]

I like to pick out a column that always has data in it, but that doesn't sound
possible in your situation.

Maybe you can rely on .specialcells to find the last row:

Option Explicit
Sub Macro1()
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange 'try to reset used range
Set myRng = .Range("a1:C" _
& .Cells.SpecialCells(xlCellTypeLastCell).Row)
On Error Resume Next
myRng.SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0
End With
End Sub

 

[foolio]

so close lol....

the problem is say i have the following columns

a b c
1 - -
2 2 -
- 2 -

and so on and so forth....the hyphens indicating blank cells....th
problem is once you run the macro it does this

a b c
1 0
2 2
0 2

i want it to be like this

a b c
1 0 0
2 2 0
0 2 0

it doesnt format the c cells.... now im not a visual basics editor bu
i have done enough programming to see that the error lies here...

Set myRng = .Range("a1:C" _
& .Cells.SpecialCells(xlCellTypeLastCell).Row)

this would work perfectly if there was for sure without a doub
something in row C .... but in my case there may not be....

anyways any (more) help would be much appreciated - Jo

 

[AlfD]

Hi!

Suppose you had a heading in column C. That would fix the column coun
issue.

Al

 

[Dave Peterson]

If you don't want to add headers, how about this:

Option Explicit
Sub Macro1()
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange 'try to reset used range
If IsEmpty(.Range("c1")) Then
.Range("c1").Value = 0
End If
Set myRng = .Range("a1:C" _
& .Cells.SpecialCells(xlCellTypeLastCell).Row)
On Error Resume Next
myRng.SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0
End With
End Sub

It checks C1 to see if it's empty. If it is, it puts a 0 and makes the
..specialcells stuff work better, too.

 

[foolio]

Perfecto my good friend Dave lol... I haven't fully tested it but i
seems to work perfectly...Thank you very much

Alf ... I can't have headers....what this is actually for is mas
updating price lists.... and the program i use to convert from excel t
our system doesnt allow you to have headers