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

Posted on 2011-03-07
Last Modified: 2012-05-11
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?
Question by:dmalovich
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
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 10

Expert Comment

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

Open in new window

LVL 50
ID: 35063344
columns, not rows, Makrini


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

cheers, teylyn
LVL 50
ID: 35063349
Or get the cell address with

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

Open in new window

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 10

Expert Comment

ID: 35063352
oops - of course - I read wrong

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.
LVL 50
ID: 35063395

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

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?
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

Author Closing Comment

ID: 35063470
Awesome. Thanks.....

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question