Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill Blank Cell Not working
I have a macro that is supposed to copy and paste data from one spreadsheet
into another in the same workbook. I need the blank cells to be filled with a space (" ") if there is not data in it. I have the macro below. TIA The macro works for the data in the ("e40:e43") range, but removes the pasted data in the ("c40:d43") range. Any ideas?? 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 Blank Cell Not working
1) this is wrong
.FormulaR1C1 = "r1c1" .Value = " " in the first line you put the letters r1c1 into the cell thenfollowed bay puttig in a spcae so all you need is .Value = " " 2) this line Range("c40:d43", Range("c" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks) is odd your range syntax is screwed With Range(Range(X),Range(Y) ) where X and Y are the topleft and bottom right cells so maybe Range( Range("c40") , Range("D" & Rows.Count).End(xlUp) ).SpecialCells(xlCellTypeBlanks) "el dee" wrote: I have a macro that is supposed to copy and paste data from one spreadsheet into another in the same workbook. I need the blank cells to be filled with a space (" ") if there is not data in it. I have the macro below. TIA The macro works for the data in the ("e40:e43") range, but removes the pasted data in the ("c40:d43") range. Any ideas?? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill a blank cell with a value using if conditions | New Users to Excel | |||
Fill cell that is blank | Excel Worksheet Functions | |||
fill blank cell with cell above: special case | Excel Programming | |||
fill blank cell with cell above: special case | Excel Programming | |||
Fill with a formula until Cell is Blank | Excel Programming |