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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top