Copy data from above

[Guest]

Hi,

Wonder if someone can help me please.

I have an excel spreadsheet where the may be data missing from some of the
cells within a row.

Could anyone tell me how I would create a macro to find these blank cells
(obviously I would need to be able to determine the columns that are
pertinent) and copy the cells from above row.

Many thanks in advance

Chris

 

[Dave Peterson]

You can use code or do it manually. (I find doing it manually is quicker!)

Debra Dalgleish has suggestions for both:
http://www.contextures.com/xlDataEntry02.html

 

[Guest]

Hi dave,

Yes i understand wher you are coming from. Unfortunately I aerage on having
to go through about 25000 rows of data, so in this instance i think a macro
would be better.

Regards

Chris

 

[Dave Peterson]

There's code at that link, too.

Hi dave,

Yes i understand wher you are coming from. Unfortunately I aerage on having
to go through about 25000 rows of data, so in this instance i think a macro
would be better.

Regards

Chris

 

[Dave Peterson]

ps.

I still think that doing it manually (even with 25000 rows) would be quicker. I
think I'd only use the macro if I were mechanizing a larger procedure and this
was just one of the steps.

 

[Guest]

Hi Dave,

Thanks for the link to the code. Can you tell me please, how could I adapt
this to copy rows rather than columns?

Many thanks

Chris

 

[Dave Peterson]

Does this mean you want to fill the empty cells with values to the left?

If you really mean that, then:

Option Explicit
Sub FillRowsBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastCol As Long
Dim myRow As Long

Set wks = ActiveSheet
With wks
myRow = ActiveCell.Row

Set rng = .UsedRange 'try to reset the lastcell
LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(myRow, 2), .Cells(myRow, LastCol)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=RC[-1]"
End If

'replace formulas with values
With .Cells(myRow, 1).EntireRow
.Value = .Value
End With

End With

End Sub

Hi Dave,

Thanks for the link to the code. Can you tell me please, how could I adapt
this to copy rows rather than columns?

Many thanks

Chris

 

[Guest]

Dave,

Thanks for replying.

The columns that I need to copy are columns A to I, moving down the sheet
copying the cells above for any blank row.

Regards

Chris

Does this mean you want to fill the empty cells with values to the left?

If you really mean that, then:

Option Explicit
Sub FillRowsBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastCol As Long
Dim myRow As Long

Set wks = ActiveSheet
With wks
myRow = ActiveCell.Row

Set rng = .UsedRange 'try to reset the lastcell
LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(myRow, 2), .Cells(myRow, LastCol)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=RC[-1]"
End If

'replace formulas with values
With .Cells(myRow, 1).EntireRow
.Value = .Value
End With

End With

End Sub

Hi Dave,

Thanks for the link to the code. Can you tell me please, how could I adapt
this to copy rows rather than columns?

Many thanks

Chris

 

[Dave Peterson]

Option Explicit
Sub FillColBlanks2()

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range("A2:I" & LastRow).Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Range("A2:I" & LastRow)
.Value = .Value
End With

End With

End Sub

Dave,

Thanks for replying.

The columns that I need to copy are columns A to I, moving down the sheet
copying the cells above for any blank row.

Regards

Chris

Does this mean you want to fill the empty cells with values to the left?

If you really mean that, then:

Option Explicit
Sub FillRowsBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastCol As Long
Dim myRow As Long

Set wks = ActiveSheet
With wks
myRow = ActiveCell.Row

Set rng = .UsedRange 'try to reset the lastcell
LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(myRow, 2), .Cells(myRow, LastCol)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=RC[-1]"
End If

'replace formulas with values
With .Cells(myRow, 1).EntireRow
.Value = .Value
End With

End With

End Sub

Hi Dave,

Thanks for the link to the code. Can you tell me please, how could I adapt
this to copy rows rather than columns?

Many thanks

Chris

:

There's code at that link, too.

ir26121973 wrote:

Hi dave,

Yes i understand wher you are coming from. Unfortunately I aerage on having
to go through about 25000 rows of data, so in this instance i think a macro
would be better.

Regards

Chris

:

You can use code or do it manually. (I find doing it manually is quicker!)

Debra Dalgleish has suggestions for both:
http://www.contextures.com/xlDataEntry02.html



ir26121973 wrote:

Hi,

Wonder if someone can help me please.

I have an excel spreadsheet where the may be data missing from some of the
cells within a row.

Could anyone tell me how I would create a macro to find these blank cells
(obviously I would need to be able to determine the columns that are
pertinent) and copy the cells from above row.

Many thanks in advance

Chris