Populating row rather than columns

johnpants

New Member
Joined
Oct 31, 2005
Messages
44
Hi, I have this code that populates the next empty cell in a column (below). I use a row as a 'record' at the minute, but if I leave a field blank on one record, the next time I come to enter a row of data one of the cells pastes into the row above because the previous entry was blank.

Code:
Sheets("order").Range("A22").copy
    Sheets("data").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
Is there a way I could amend this so that it looks at column A for example, then pastes to the next empty cell to the right of the original entry etc.?

Does this make sense?

Thanks.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello johnpants,
I'm guessing there is more to your code than what's shown here, and that you're doing the same thing with one or more additional columns.

Let's say you have the same code also working on column C.
Column C was left blank last time, so now in the data sheet, column A goes to row 1000 and column C only goes to row 999, so when you run the code now the new order sheet cell A22 goes to data sheet, cell A1001 and order sheet cell C22 goes to data sheet row 1000. (That sound about like what it's doing?)

If that's the case then you can do one of a couple things. In the code for your other column(s), have it look for the last used cell in data sheet column A, (since the new value has just been put there from the previous bit of code) and just use that row (ie. - using your example code above)
Code:
Sheets("order").Range("A22").Copy
    Sheets("data").Select
    NextRow = Range("A65536").End(xlUp).Row + 1
    Range("A" & NextRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
   End With
Sheets("order").Range("C22").Copy
    Sheets("data").Select
    NextRow = Range("A65536").End(xlUp).Row
    Range("C" & NextRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
   End With
Now, with that having been said (and still assumming I'm on the right track...), you can do this quicker and with less code if you don't select the cells and /or sheets. This code will do the same as the code in my example above and take care of the problem (as I understand it) that you've asked about.
Code:
Dim NextRow As Long

NextRow = Sheets("data").Cells(Rows.Count, 1).End(xlUp)(2, 1).Row
Sheets("data").Range("A" & NextRow).Value = Sheets("order").[A22].Value
Sheets("data").Range("C" & NextRow).Value = Sheets("order").[C22].Value
With Sheets("data").Range(("A" & NextRow), ("C" & NextRow))
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
That help at all, or am I on the wrong track? (If I am then you might want to post more of your code.)

Dan
 

johnpants

New Member
Joined
Oct 31, 2005
Messages
44
Thank you very much, that has solved the problem perfectly!

Appreciate the help.

John.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,268
Messages
5,577,102
Members
412,768
Latest member
klig
Top