Excel - Finding the next blank cell in a row.

  Sandhurst1 10:38 21 Feb 08
Locked

Hi

I am in the middle of my ICT project for college and have hit a problem. I am trying to copy data from one sheet to another sheet using a macro.

The macro goes though a list on one sheet finding where the cell is > 0, when it finds one, it copies the cells (B:G). It then selects the second sheet and pastes special into one cell. It then goes and continues going through the list, when it finds the next data to be copied, it copies it and then goes to the second sheet to paste it. How can I make it paste in the next available cell now that the first is taken. Ie: Paste into G3 now F3 is taken. Or paste into J3 because F3:I3 are all taken?

Many thanks in advance.

  VoG II 18:42 21 Feb 08

Like this

Sub test()
Dim NextCol As Integer
NextCol = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
Sheets("Sheet1").Range("B3:G3").Copy
Aheets("Sheet2").Cells(3, NextCol).PasteSpecial Paste:=xlPasteValues
End Sub

  VoG II 18:43 21 Feb 08

Sub test()
Dim NextCol As Integer
NextCol = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
Sheets("Sheet1").Range("B3:G3").Copy
Sheets("Sheet2").Cells(3, NextCol).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

  VoG II 18:44 21 Feb 08

NextCol = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column + 1

  Sandhurst1 19:04 21 Feb 08

Thanks for that, really helpful, cheers

  VoG II 19:18 21 Feb 08

You might find this handy for future reference click here

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac