Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down - adjust code
I am currently using two identical codes (like below) to fill in
blanks in column A. (subsequent-identical code for Column B). Any ideas on combining them both? Need to run one code that fills in blanks in both columns A & B. Thanx. Sub FillColBlanksA() Dim wks As Worksheet Dim Rng As Range Dim lastrow As Long Dim col As Long Set wks = ActiveSheet With wks col = .Range("A1").Column Set Rng = .UsedRange 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 With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down - adjust code
JW,
This can be done rather simply with only a couple of changes. Try passing the column as an argument... Then, call the sub from a seperate sub with the following. Call it again with a new column_number to perform it on a second (or more) column(s): ' Code before this point left out... Call FillColBlanks(column_number) ' Code after this point left out... Then make the following changes (added the argument being passed and two lines commented out): Sub FillColBlanks(col As Long) Dim wks As Worksheet Dim Rng As Range Dim lastrow As Long 'Dim col As Long <-----Now passed as argument Set wks = ActiveSheet With wks 'col = .Range("A1").Column <----Now passed as argument Set Rng = .UsedRange 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 With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub David "J.W. Aldridge" wrote in message ups.com... I am currently using two identical codes (like below) to fill in blanks in column A. (subsequent-identical code for Column B). Any ideas on combining them both? Need to run one code that fills in blanks in both columns A & B. Thanx. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down - adjust code
Thanx...
However, I am not that good with French yet. I only speak English and a little Spanish. (In other words.... I'm good at deciphering code given, but dont quite got the lingo and understanding of putting it together). Is there any simpler way to guide me? I just need to run the code I gave above on columns A & B instead of one column - or instead having to run two different macro's to get this done. Thanx Again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help to adjust my code | Excel Programming | |||
Help to adjust code | Excel Programming | |||
HELP - I need to adjust code!!!! | Excel Programming | |||
Adjust a Scroll bar to fill in a range perfectly | Excel Programming |