.End(xlDown)

[Devine]

Hi all,
I have been struggling for a while to get this to work so if anyone has the answer or an alternative way of doing this it would be much appreciated.

I currently have a spread sheet with groups of data down a number of columns,
I have a User Form wth a pick list, the user selects a value.
the macro then uses the value from the user form to place a yes by the data which is linked to said value.

E.g User selects TWO

ONE | Value |
ONE | Value |
ONE | Value |

ONE | Value |

TWO | Value | Yes
TWO | Value | Yes
TWO | Value | Yes

TWO | Value | Yes
TWO | Value | Yes

THREE | Value |
THREE | Value |
THREE | Value |

THREE | Value |

The macro then finds the yes value, offsets to the left, selects down to a blank, copys the Data, deletes the Yes beside the data, pastes the data into another sheet, then goes around again to find yes select data etc etc.

The problem i have is the data beside it is grouped and i want the macro to stop at the blank and select the group of data, this works fine if there is more than one value in the group, But if there is only one value in the group when using the End(xldown) statement the selection jumps past the next blank cell and takes the next value below with it.

E.g

ONE | Value |
ONE | Value |
ONE | Value |

TWO | Value | Yes
TWO | Value | Yes

TWO | Value | Yes
TWO | Value | Yes
TWO | Value | Yes

TWO | Value | Yes

THREE | Value | - Takes this value
THREE | Value |
THREE | Value |

See below for VBA code:

Cells.Find(What:="YES").Activate
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 1).ClearContents
Selection.Copy

As i said if anyone has an alternative way of selecting the data, it would be much appreciated!

Thanks

 

[tweedle]

I'm thinking there are better methods, but in the current scheme; Could wrap it in an if statement:

If ActiveCell.Offset(1, -1) <> "" Then
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(0, 1).ClearContents
    Selection.Copy
Else
    Selection.Offset(0, 1).ClearContents
    Selection.Copy
End If