Finding Adjacent Empty Cell

keithd · July 14th, 2006, 12:49 PM

Hello all,

lets say you have 1 column (A) of data and it goes to row 5, You insert a new Column A and shift everything to the right. So now you have a blank Column A and a Column B that has 5 rows of data.

How do I find the cell in Column A that would match the last row of data in Column B which would be 5, So that i can fill Column A with data

I was thinking i can find the last cell of data in Column B. could i than move the cell selector over to the left by 1 and fill up? And if so how do i do that?

currently this is what i have but it's not working out so well

Code:

Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight



    Columns("B:B").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    lastCell = ActiveCell.Address
    'RangeObject.Insert (xlShiftToRight)

    Range("A1:" & lastCell).Select

Thanks in advance. I hope it is clear

Oh i'm using Excel Xp

Thanks
Keith

echovue · July 14th, 2006, 12:54 PM

This is how I would do it, but there could very well be a better way...

Code:

i = 1
While Columns("B" & i).Value = ""
    i = i + 1
Wend

Columns("A" & i).Select

Selection.Value = "Whatever you want to put in here!:"

HTH

Mike

Mike
EchoVue.com

keithd · July 14th, 2006, 02:08 PM

Mike,

Thanks so much for that. It's amazingly simple, my way was um slightly complex and very dumb.

When i step through the code though i'm getting

Run-time error '1004':
Application-defined or object-defined error

I used

Debug.Print i = 1
While Columns("B" & i).Value = ""
Debug.Print i = i + 1
    Wend

    Columns("A" & i).Select

    Selection.Value = "Whatever you want to put in here!:"

and as I stepped into the loop it said false in the intermediate window and gave me an error

Run-time error '13':
Type mismatch

I'm not sure what's going wrong

Thanks,
Keith

keithd · July 14th, 2006, 03:04 PM

So i've made some changes to make the error go away.

I've done a little looking into and changed the loop to look like this. Only cause this compiles

Code:

i = 1
Do While Not IsEmpty("B" & i)
debug.print i    
    i = i + 1
Loop
Columns("A" & i).Select

It doesn't error, But when i run it, the loop runs till it crashes. I put the Debug.print i in and it counts up the rows till it crashes. Even though there is information in col b it never stops when it reaches an empty cell.
here is my table
A B
gg gg
dd dd
dd dd
vv vv
ff ff
hh hh
jj jj

Am i not understanding the way IsEmpty works?

echovue · July 14th, 2006, 03:17 PM

I Have never use isEmpty before, I'm afraid. If you were to go back to the previous code. On which lines are you getting the errors.

Error 13, generally means it doesn't know which sheet to to.

Try instead of Columns("A" & i)

While ActiveSheet.Range("A" & i).Value <> ""

Let me know how that goes.

Mike

Mike
EchoVue.com

keithd · July 14th, 2006, 03:28 PM

That was the ticket. I missed the active.sheet but it works perfectly.

Thanks for you help.

Keith

echovue · July 14th, 2006, 03:36 PM

Anytime! Have a great weekend!

Mike

Mike
EchoVue.com

SMI · July 19th, 2006, 06:20 AM

Dear all
I guess the approach could be using one line of code as follows:

Range("B1").End(xlDown).Offset(0, -1).Value = "what ever you want"

Try this and let me know if you have any problem.

SMI

Genius is 99% perspiration and 1% inspiration