+ Reply to Thread
Results 1 to 9 of 9

Copy data from above

  1. #1
    ir26121973
    Guest

    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

  2. #2
    Dave Peterson
    Guest

    Re: 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

  3. #3
    ir26121973
    Guest

    Re: 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
    >


  4. #4
    Dave Peterson
    Guest

    Re: 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

  5. #5
    Dave Peterson
    Guest

    Re: 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

  6. #6
    ir26121973
    Guest

    Re: 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
    >


  7. #7
    Dave Peterson
    Guest

    Re: 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

  8. #8
    ir26121973
    Guest

    Re: 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
    >


  9. #9
    Dave Peterson
    Guest

    Re: 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1