Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Fill Blank Cells Macro
Hello,
I searched the macro for fill blank cells . May I know how to modified the following macro, therefore, it can apply on column B, C and E? Thanks Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 'http://www.contextures.com/xlDataEntry02.html Dim wks As Worksheet Dim rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = activecell.column 'or 'col = .range("b1").column Set rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set rng = Nothing On Error Resume Next Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Fill Blank Cells Macro
I searched the macro for fill blank cells . May I know how to
modified the following macro, therefore, it can apply on column B, C and E? Thanks <<<code snipped Does this code do what you want... Sub FillColBlanks() Dim Col As Variant, Data As Range, A As Range For Each Col In Array("B", "C", "E") Set Data = Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp)) For Each A In Data.SpecialCells(xlCellTypeBlanks).Areas A.Value = A(1).Offset(-1).Value Next Next End Sub Rick Rothstein (MVP - Excel) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Fill Blank Cells Macro
Sorry, I forgot to include the error trapping. Use this code instead...
Sub FillColBlanks() Dim Col As Variant, Data As Range, A As Range On Error GoTo NoCellsFound For Each Col In Array("B", "C", "E") Set Data = Range(Cells(1, Col), Cells(Rows.Count, Col).End(xlUp)) For Each A In Data.SpecialCells(xlCellTypeBlanks).Areas A.Value = A(1).Offset(-1).Value Next Next NoCellsFound: End Sub Rick Rothstein (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to fill blank cells | Links and Linking in Excel | |||
Macro to transpose data to fill blank cells in table | Excel Programming | |||
Use Autofill in a macro to fill blank cells until next text - | Excel Programming | |||
Use Autofill in a macro to fill blank cells until next text - | Excel Programming | |||
A macro to fill in the blank cells | Excel Programming |