Copy cell to first BLANK cell in column, NOT the first blank cell from the last row used

E

Eyeson15

Board Regular

Joined

Apr 30, 2015

Messages

201

• Sep 17, 2016

• #2

I sort of found this which works so anyone else looking for solution to similar problem, try it.

I wonder if there is a simpler solution.

Code:

Sub kansdasdasdasdasds()

    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
    Set r1 = Sheets(1).Range("A1")
    Set r2 = Sheets(2).Range("B1:B100")
    For Each r3 In r1
    If r3.Value = "" Then
        r2.Copy
        r3.PasteSpecial xlPasteValuesAndNumberFormats
        Exit Sub
    End If
    Next
    
End Sub

 

Last edited: Sep 17, 2016

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Sep 17, 2016

• #3

You can copy your unspecified cell to this cell...

Columns("B").SpecialCells(xlBlanks)(1)

For example, if the cell with the data you want to copy is cell A1, then you would write this...

Range("A1").Copy Columns("B").SpecialCells(xlBlanks)(1)

 

Last edited: Sep 17, 2016

Upvote 0

My Aswer Is This

Well-known Member

Joined

Jul 5, 2014

Messages

19,511

Office Version

1. 2021

Platform

1. Windows

• Sep 17, 2016

• #4

Try this:
Copies value in Range("G1") to first empty cell in column "B"

Code:

Sub Copy_Me_To_Column_B_First_Empty()
Dim ans As Long
ans = Columns(2).End(xlDown).Row + 1
Range("G1").Copy Destination:=Range("B" & ans)
End Sub

 

Upvote 0

E

Eyeson15

Board Regular

Joined

Apr 30, 2015

Messages

201

• Sep 17, 2016

• #5

Hi Rick

Thank you for your input.

It still skips over 'some' of the blanks. I also tried .SpecialCells(xlCellTypeBlanks)(1) and it skips over some of the blank cells. Not sure if this information helps but these blank cells does not have any formula but does have conditional formatting.

James

 

Upvote 0

My Aswer Is This

Well-known Member

Joined

Jul 5, 2014

Messages

19,511

Office Version

1. 2021

Platform

1. Windows

• Sep 17, 2016

• #6

Are you using this in a loop? You should show us your entire script.

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Sep 17, 2016

• #7

Eyeson15 said:

It still skips over 'some' of the blanks. I also tried .SpecialCells(xlCellTypeBlanks)(1) and it skips over some of the blank cells.

Click to expand...

Then those cells are not blank (they may have invisible characters such as spaces in them or, alternately, they may have formulas in them which displays the empty text string "").

 

Upvote 0

E

Eyeson15

Board Regular

Joined

Apr 30, 2015

Messages

201

• Sep 17, 2016

• #8

Hi My Aswer.

Thanks again for your input.

Same problem as Rick's code. It skips over some of the blank cells. These blanks do not have any formula but does have conditional formatting.

Also.. is it possible to .PasteSpecial xlPasteValuesAndNumberFormats because there are formulas in the cell I'm copying from.

James

 

Upvote 0

E

Eyeson15

Board Regular

Joined

Apr 30, 2015

Messages

201

• Sep 17, 2016

• #9

Ahh I get it.

So they are NOT blanks to start with. Lesson learnt.

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Sep 17, 2016

• #10

Eyeson15 said:

Ahh I get it.

So they are NOT blanks to start with. Lesson learnt.

Click to expand...

Maybe if you tell us more about your setup/layout and data then perhaps we can help you achieve the ultimate result you want.

 

Upvote 0