Excel - Finding the next blank cell in a row.

  Sandhurst1 10:38 21 Feb 2008
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 2008

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 2008

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 2008

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

  Sandhurst1 19:04 21 Feb 2008

Thanks for that, really helpful, cheers

  VoG II 19:18 21 Feb 2008

You might find this handy for future reference click here

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

Elsewhere on IDG sites

WPA2 Hack Latest News: How Secure is your Wi-Fi?

Photoshop CC 2018 released with new Curvature Pen and better brush tools

Best kids apps for iPhone & iPad

Comment utiliser Twitter ?