Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill empty cell macro not working
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 in a range using the used cell | Excel Programming | |||
Loop to find next empty cell to the right not working. | Excel Programming | |||
fill cell value to the next empty row | Excel Discussion (Misc queries) |