Empty row

[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!




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

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

 

[Myrna Rodriguez]

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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

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

 

[Myrna Rodriguez]

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





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

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

 

[Myrna Rodriguez]

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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

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

 

[Myrna Rodriguez]

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bernie Deitrick]

enjoy life!

I do! And you're quite welcome.

Bernie