Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill empty cells in a range using the used cell
I have a macro that Color codes all the empty cells in the Range and enters
the legend Missing! to show what attributes need to be entered. Sub FillEmptyCells() WCount = Worksheets.Count For i = 1 To WCount If Worksheets(WCount - i + 1).Visible Then Worksheets(WCount - i + 1).Select RCount = ActiveCell.SpecialCells(xlLastCell).Row CCount = ActiveCell.SpecialCells(xlLastCell).Column For j = 1 To RCount For k = 1 To CCount If IsEmpty(Worksheets(WCount - i + 1).Cells(j, k)) Then Worksheets(WCount - i + 1).Cells(j, k) = "Missing!" Worksheets(WCount - i + 1).Cells(j, k).Interior.ColorIndex = 35 Worksheets(WCount - i + 1).Cells(j, k).Font.Bold = True End If Next k Next j End If Next i End Sub The idea is that my spreadhsheets will be shrinking over the time because less number of cells will have missing attributes. The above macro worked well for a while until my spreadsheets started shrinking and some of the last rows were all filled with Missing! and color coded, I think it was because somehow Excel detected them as "active" although they have no values. I read and article about the "Last Cell" only being reset when you save and the user who looks at the excel file can't save it (I made that intentionally) so I guess my problem has to do with that. On other website I found the sintax to select the Last Used Cell in a row or column Sub LastCellInRow() Range("IV1").End(xlToLeft).Select End Sub Sub LastCellInColumn() Range("A65536").End(xlUp).Select End Sub But I don't know how to include this as the range on the previous macro, in short, I need my range to be from A2 to the last used Column,Row. I hope it is not to confusing and I appreciate any help. Thanks Bruno |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill empty cells in a range using the used cell
The following uses the usedrange which is from the first cell used to the
last cell used. It may be ok and easier to follow. Sub FillEmptyCells() Dim ws As Worksheet Dim r As Range For Each ws In ActiveWorkbook.Worksheets If ws.Visible Then If ws.UsedRange.Address < "$A$1" Then For Each r In ws.UsedRange If IsEmpty(r) Then r.Value = "Missing!" r.Interior.ColorIndex = 35 r.Font.Bold = True End If Next r End If End If Next ws Set r = Nothing Set ws = Nothing End Sub -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "bdmsfan" wrote: I have a macro that Color codes all the empty cells in the Range and enters the legend Missing! to show what attributes need to be entered. Sub FillEmptyCells() WCount = Worksheets.Count For i = 1 To WCount If Worksheets(WCount - i + 1).Visible Then Worksheets(WCount - i + 1).Select RCount = ActiveCell.SpecialCells(xlLastCell).Row CCount = ActiveCell.SpecialCells(xlLastCell).Column For j = 1 To RCount For k = 1 To CCount If IsEmpty(Worksheets(WCount - i + 1).Cells(j, k)) Then Worksheets(WCount - i + 1).Cells(j, k) = "Missing!" Worksheets(WCount - i + 1).Cells(j, k).Interior.ColorIndex = 35 Worksheets(WCount - i + 1).Cells(j, k).Font.Bold = True End If Next k Next j End If Next i End Sub The idea is that my spreadhsheets will be shrinking over the time because less number of cells will have missing attributes. The above macro worked well for a while until my spreadsheets started shrinking and some of the last rows were all filled with Missing! and color coded, I think it was because somehow Excel detected them as "active" although they have no values. I read and article about the "Last Cell" only being reset when you save and the user who looks at the excel file can't save it (I made that intentionally) so I guess my problem has to do with that. On other website I found the sintax to select the Last Used Cell in a row or column Sub LastCellInRow() Range("IV1").End(xlToLeft).Select End Sub Sub LastCellInColumn() Range("A65536").End(xlUp).Select End Sub But I don't know how to include this as the range on the previous macro, in short, I need my range to be from A2 to the last used Column,Row. I hope it is not to confusing and I appreciate any help. Thanks Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste to next empty cell in named range of cells | Excel Discussion (Misc queries) | |||
Find empty cells, fill with text from cell above | Excel Worksheet Functions | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
Fill empty cells | Excel Programming | |||
Fill empty cells | Excel Programming |