Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-07
9
Medium Priority
?
296 Views
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?
0
Comment
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
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

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

Open in new window

0
 
LVL 50
ID: 35063344
columns, not rows, Makrini

try

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

cheers, teylyn
0
 
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

0
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.

 
LVL 10

Expert Comment

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

Author Comment

by:dmalovich
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
ID: 35063395
like

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

Author Comment

by:dmalovich
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

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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

by:dmalovich
ID: 35063470
Awesome. Thanks.....
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

610 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