Find Next Non Blank Cell In Column Then ....

[JRidge]

Hi All,

I have been trying to do this for sometime now and keep coming up blank..

I am Writing a Code...

In this code i have used A Lookup type line to find a Cell in Column A with a Specific String in.. All Good So Far....

I then Activate the Cell containing this String.. So Say it was in A2 Then A2 would now be the ActiveCell.

What i need to do now is as Follows..:

1. I need to Find the NEXT NON Blank cell after the ActiveCell. Say this was A9

2. I need to Select the Range(ActiveCell(A2): Next Non BlankCell -1)

3. Once this has been Done i need to Resize the Selected Range to incorporate The Cells In Columns B,C,D and then Copy this Final Range which would be A2:D8

	A	B	C	D
1
2	String
3
4
5
6
7
8
9	2nd String

Any Help would be greatly appreciated.

Regards

JRidge

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

Just a point but try and avoid code which activates or selects ranges/sheets. It's rarely necessary and just slows things down. Just define the ranges/sheets in the code.

[JRidge]

But i don#t know the Range until i have done the Find and selected the Cell?

The string i am searching is always in column A, There might or Might Not be Blank Cells Between the Found Cell in A and the Next Cell that is not Blank. I need to Copy from the Active Cell to the Cell before the next cell that is non Blank and these cells in B,C and D aswell?

With Your Help I now have:

Please Login or Register  to view this content.

Which works.

[Richard Buttrey]

But i don#t know the Range until i have done the Find and selected the Cell?

...but that isn't what you said in the original. There you said that you started with the active cell and then you said...
"What i need to do now is as Follows..:

The 'as follows' was to copy A2:D8 which is precisely what my single line of code does.

[JRidge]

Hi Richard,

In this Example:

	A	B	C	D
1
2	String
3	2nd String
4	String3
5	String4
6	(Merged With A7)String5
7	(Merged with A6)
8	String6
9	String7

When i use your Line of code:

Please Login or Register  to view this content.

in this instance..

Active Cell is A2

It allways copies down to the Merged Cell... It Sees the Merged cell as the next Non Blank Cell? So It copies A2:D5

When it should only copy A2:D2


But If the ActiveCell was The Cell with String5 in then it works fine and only Copies A6:D7
Any Ideas...

Regards

JRidge