• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

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

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
dmalovich
Asked:
dmalovich
  • 4
  • 3
  • 2
1 Solution
 
MakriniCommented:
 Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = "This Was blank"

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
columns, not rows, Makrini

try

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

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Or get the cell address with

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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MakriniCommented:
oops - of course - I read wrong
0
 
dmalovichAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
like

MsgBox Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Column
0
 
dmalovichAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I'll try again. Maybe I have invisible ink :)

num = Worksheets("Rank").Cells(7, Columns.Count).End(xlToLeft).Offset(0, 1).Column
0
 
dmalovichAuthor Commented:
Awesome. Thanks.....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now