Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting empty cells
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(cell0;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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting empty cells
Post your macro
"LiAD" wrote: 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(cell0;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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting empty cells
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 "Mike H" wrote: Post your macro "LiAD" wrote: 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(cell0;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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting empty cells
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" wrote: 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 "Mike H" wrote: Post your macro "LiAD" wrote: 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(cell0;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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Deleting empty cells
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 "LiAD" wrote: 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" wrote: 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 "Mike H" wrote: Post your macro "LiAD" wrote: 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(cell0;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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows Between Empty Cells | Excel Worksheet Functions | |||
deleting empty rows | Excel Discussion (Misc queries) | |||
Deleting All Empty Rows | Excel Discussion (Misc queries) | |||
Sorting and deleting empty cells | Excel Discussion (Misc queries) | |||
Deleting unwanted and empty rows.... | New Users to Excel |