Deleting empty cells

[LiAD]

Hi,

I am using a macro to turn a table into a text string. Example:

A 1 4
B 2
C 1

Into A B B C A A A. I am then using a conditional format to colour the
cells according to the letter inside.

However the above table is the output from another worksheet, which the only
automatic way I can generate is by =if(cell>0;cell;â€â€) so that I do not have
zeros in the table. However the macro does not like cells with the IF
formula and no number. So for example in the above table if in the first
column against B I have the IF formula the macro will not work, the cell must
either have a number or be completely empty. The only way it works is if I
go through by hand and delete the formula from the empty cells then use the
macro.

Can I use a function or macro to do this delete operation for me? The
tables are quite big and will get changed quite often so the hand delete is
time consuming.

Thanks

 

[Mike H]

Post your macro

 

[LiAD]

Thanks

Sub Machine1()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(9, j), Cells(12, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(9, j)) Then simbol = "R"
If Not IsEmpty(Cells(10, j)) Then simbol = "L"
If Not IsEmpty(Cells(11, j)) Then simbol = "W"
If Not IsEmpty(Cells(12, j)) Then simbol = "N"

If IsEmpty(Cells(25, 3)) Then
n = 3
Else
n = Cells(25, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(25, k + n - 1).Value = simbol
Next
Next
End Sub

LiAD

 

[LiAD]

As there are two macros for doing one job (make the horizontal line of text
and then colour it in), is it better to have one macro that does both rather
than two separate ones?

Just for reference on my worksheet I have three sets of tables filling in
three sets of coloured text strings so I will change the base macro to fit
each of the three locations.

Thanks a lot for your help, again.

 

[LiAD]

Sorry for keeping adding bits on but i've just relaised one of the benefits
of conditional formatting - i can hide the text by having it the same colour
as the cell.

Is this possible with VBA as well?

Either that or I guess it might be easier to say put the text in row 10 and
the coloured areas in row 11?

Thanks