Copy data from above
 

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

Copy data from above
 

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

--

Dave Peterson

Copy data from above
 

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" wrote:

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

--

Dave Peterson

Copy data from above
 

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson

Copy data from above
 

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.

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson

Copy data from above
 

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" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson

Copy data from above
 

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

ir26121973 wrote:

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" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Copy data from above
 

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

"Dave Peterson" wrote:

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

ir26121973 wrote:

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" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Copy data from above
 

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

ir26121973 wrote:

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

"Dave Peterson" wrote:

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

ir26121973 wrote:

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" wrote:

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

"Dave Peterson" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson