Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste
I am attempting to take data that looks like this.
A B C D E F 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 0.2 0.2 Look for if there is any data in Col F (data is variable) and Col E is blank then copy the previous Row's data A-E and paste it in the next row. So it then looks like this. A B C D E F 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 Any help with this would be great! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro to check if cell is not blank & previous cell is blank,copy information from row above & paste
Maybe you could try this:
Select A2:F### (through the last used row) Edit|Goto|special|blanks Notice that the selection is now just those blank cells from the original selection. Now type the = sign and hit the uparrow Hit ctrl-enter to fill all those empty cells with a formula that points to the value above. Debra Dalgleish has some more instructions: http://contextures.com/xlDataEntry02.html JenIT wrote: I am attempting to take data that looks like this. A B C D E F 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 0.2 0.2 Look for if there is any data in Col F (data is variable) and Col E is blank then copy the previous Row's data A-E and paste it in the next row. So it then looks like this. A B C D E F 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 125.06 3.12 26.05 2/5/2007 7/1/2007 0.2 Any help with this would be great! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste
Hi Dave:
Thanks for the prompt response. I must have this all work within a macro - so coding is what I am after. And by looking at the link I believe there is a lot for me to use in your coding however I am coming up short because of my inexperience with this. I must replace the blanks yet to be careful not to replace the number in col F as that can be a variable. I used below - When I try use this line...col = .range("A2:E3000").column - it replaces Col A only (my sheet will always vary in length) And if I use it with active cells only...it will replace the blanks in the first column as well and nothing else. No matter what critera I use it does always break at .value = .value THOUGHTS?? Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste
Hi Dave:
Thanks for the prompt response. I do need code as I need this to work all within a macro. I followed the link and your code is on track but no matter if I use the col = activecell.column 'or 'col = .range("A2:E3000").column - the lenth of my sheet is a variable It always only replaces in col A and then when it gets to the bottom of .value = .value it breaks. I am fairly new to VB so I could use any guidance you have. I need to replace my blanks in A:E however col F must remain in tact as that is sometimes a variable. - Below is the code from the link that I used Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need macro to check if cell is not blank & previous cell is blank,copy information from row above & paste
Did you try it manually? If it didn't work the way you wanted, then don't do
this! How about looking in column F to find the last used row. Then fill every blank cell in A2:E(lastrow) with a formula that points at the cell above. The last portion converts all the formulas in A:E to values. Option Explicit Sub FillColBlanks() Dim wks As Worksheet Dim rng As Range Dim LastRow As Long Set wks = ActiveSheet With wks LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row Set rng = Nothing On Error Resume Next Set rng = .Range("A2:E" & LastRow).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 'convert A:E to values??? With .Range("A2:E" & LastRow) .Value = .Value End With End With End Sub JenIT wrote: Hi Dave: Thanks for the prompt response. I must have this all work within a macro - so coding is what I am after. And by looking at the link I believe there is a lot for me to use in your coding however I am coming up short because of my inexperience with this. I must replace the blanks yet to be careful not to replace the number in col F as that can be a variable. I used below - When I try use this line...col = .range("A2:E3000").column - it replaces Col A only (my sheet will always vary in length) And if I use it with active cells only...it will replace the blanks in the first column as well and nothing else. No matter what critera I use it does always break at .value = .value THOUGHTS?? Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If blank cell, copy information from cell above | Excel Worksheet Functions | |||
Macro to copy previous row and insert two blank rows | Excel Discussion (Misc queries) | |||
Copy to first Blank cell in Colum C Non blank cells still exist be | Excel Programming | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
Copy & paste cell after inserting blank row | Excel Programming |