Fill empty cell macro not working
First, I can't think of anytime I'd want to put " " in a cell. It could screw
up lots of other formulas:
=if(c1="","looks blank", "doesn't look blank")
I'd have to rewrite it to:
=if(trim(c1)="","looks blank", "doesn't look blank")
And it could mess up my counta's, countif's, ...
Maybe...
on error resume next 'in case there are no empty cells
With Range("c40:d" & Range("c" & Rows.Count).End(xlUp).row) _
.SpecialCells(xlCellTypeBlanks)
.Value = " "
End With
on error goto 0
I don't understand what the purpose of this line is:
..FormulaR1C1 = "r1c1"
And I don't think you want this:
..Columns("c").MergeCells = False
It's doesn't refer to column C of the worksheet. It refers to column C based on
the top left cell of that with statement. So it would be column E of the
worksheet.
el dee wrote:
I have a macro that cuts and pastes data from one spreadsheet to another. I
need to fill blank cells with a space (" ") before the paste. I have the
macro posted below.
It works for the ("e40:f43") range but is not working for the ("c40:d43")
range. When I run the macro with the (With ("c40:d43") range) statement, it
does not paste the data at all into the "d" columns, but the "f" columns
paste work fine.
Any ideas?
TIA
With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("Plot_Dominant_Rip_Veg")
End With
With Range("c40:d43", Range("c" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks)
.FormulaR1C1 = "r1c1"
.Value = " "
.Columns("c").MergeCells = False
End With
With Range("e40:f43", Range("e" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks)
.FormulaR1C1 = "r1c1"
.Value = " "
.Columns("e").MergeCells = False
End With
Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste
Set rngCopy = SetCopyRange(wksCopy, "C40:D43")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
rngCopy.Copy rngPaste
Set rngCopy = SetCopyRange(wksCopy, "E40:F43")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "F")
rngCopy.Copy rngPaste
With Sheets("Plot_Dominant_Rip_Veg")
LastRow = .Cells(.Rows.Count, "f").End(xlUp).Row
'using the last used cell in column A to get the row to copy
Set RngToCopy = .Cells(.Rows.Count, "A").End(xlUp)
HowManyRows = LastRow - RngToCopy.Row + 1
If HowManyRows 1 Then
RngToCopy.Resize(HowManyRows, 2).FillDown
End If
.Columns("A").MergeCells = False
End With
--
Dave Peterson
|