Solved

# I'm trying to find the first empty cell in a row in excel using vba

Posted on 2011-03-07
289 Views
I have dynamically generated data add to row 7.  For example if I have data in columns A,B,C,D of row 7 how can I find out that E7 is the next empty cell in the row?
0
Question by:dmalovich
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2

LVL 10

Expert Comment

ID: 35063331
`````` Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = "This Was blank"
``````
0

LVL 50

Expert Comment

ID: 35063344
columns, not rows, Makrini

try

Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1) = "This was blank"

cheers, teylyn
0

LVL 50

Expert Comment

ID: 35063349
Or get the cell address with

``````MsgBox Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Address
``````
0

LVL 10

Expert Comment

ID: 35063352
oops - of course - I read wrong
0

Author Comment

ID: 35063364
I tried it and when I try to add data to the row it adds it in column E.  What I'm looking for is to return a number that represents the next empty cell.  Example if A,B,C have data how can I return the number 4 because I want to enter data in to column D ( D representing the 4th column).  During the next
iteration I want to put data into E representing the 5th column.  I hope I'm explaining it enough.  Let me know if you have any questions and thanks for responding so quickly.
0

LVL 50

Expert Comment

ID: 35063395
like

MsgBox Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Column
0

Author Comment

ID: 35063439
If I have something in A7,B7,C7,D7 and I want to put something in E7 I would like to find out how to return the number 5 representing the 5th column of row 7.

num = Worksheets("Rank").Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1) returns 0.  How can I return 5 for example to represent E7?
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 total points
ID: 35063452
I'll try again. Maybe I have invisible ink :)

num = Worksheets("Rank").Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Column
0

Author Closing Comment

ID: 35063470
Awesome. Thanks.....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month5 days, 11 hours left to enroll