Empty row

  • Thread starter Thread starter Myrna Rodriguez
  • Start date Start date
M

Myrna Rodriguez

Good morning...

How can I bypass empty cell to proceed to next row even if empty cells
exist?

For Example:

col A B C
row
1 red blue green
2 red
3 red blue
I want to be able to populate to row as in this example.

Code I wrote to go to next row:
Nextrow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 2
Cells(Nextrow, 1) = txtfast.Text
This works but validates empty cell as next row.

thanks & have a happy day!
girl on a mission!
 
Myrna,

Your example isn't clear. Show examples of what you start with and what you
want to end with.

HTH,
Bernie
MS Excel MVP
 
In excel, I want to move to next row.
I noticed if cells are empty in the previous row,
next row will setfocus on previous row empty cells.
I designed a userform for data entry to excel worksheet.
The textbox fields are being populated & proceeds to next rows. Although
there may be a row consisting of empty fields. I need to bypass these
empty fields.

thanks again for your help.
 
Myrna,

No more words.... Simply show me what you are starting with, and what you
want to end with:

Start with your previous example:

col A B C
row
1 red blue green
2 red
3 red blue

Copy and then edit this to show what you want to end with.

HTH,
Bernie
MS Excel MVP
 
Scenario:

col A B C
row
1 red blue green
2 red
3 red blue
4 red blue green ****this is my desired result
ROW 4

next empty row should be ROW 4, bypassing empty fields
in ROWS 2 & 3

pardon for lack of clarity. is this clear?
Thanks much Bernie for you immediate response.
I'm sure this can be tackle, my mind is pondering...
 
Myrna,

If your data set starts in cell A1, then you can do something like

Dim Nextrow As Long
Dim myRange As Range

Set myRange = Range("A1").CurrentRegion
Nextrow = myRange(myRange.Cells.Count).Row +1

Cells(Nextrow, 1) = txtfast.Text
Cells(Nextrow, 2) = ....
Cells(Nextrow, 3) = ....

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

Thanks this code works successfully!
It goes to next row & appends to previous fields
if left empty. As my example below, the data is being
inserted in B2 & C2.
I need a way to skip over empty fields in a row & move forward. thanks
for your patience & guidance!

Set myRange = Range("A1").CurrentRegion
Nextrow = myRange(myRange.Cells.Count).Row +1

Cells(Nextrow, 1) = txtfast.Text
Cells(Nextrow, 2) = ....
Cells(Nextrow, 3) = ....

HTH,
Bernie
MS Excel MVP
 
Myrna,

Does it work successfully? It seems you don't want it to insert data in B2
and C2, and if anything in row 3 is filled in, it shouldn't (and doesn't
when I test.)

After the line

Nextrow = myRange(myRange.Cells.Count).Row +1

insert the code
Msgbox "The row I will write to is row #" & nextrow

then run it and see what it says. For your example data setup, it should
say
"The row I will write to is row #4"

Otherwise, it really isn't working.

HTH,
Bernie
MS Excel MVP
 
Wow...
was in the process of responding!
Bernie...mission accomplished, so exciting.
The code works successfully.
You been a delight in accomplishing my mission!
Thanks for you patience as I was bugging you like vba code!

enjoy life!
myrna
 
Back
Top