finding first blank dell in specified range

[samkeenan]

I'm trying to write code to cut and paste a single selected cell into the first blank cell in a specified range (eg H4:H18) but I've been using (xlUp) which isn't returning the desired effect. I don't think (xlUp) is the solution to my problem, but I'm not sure how else to go about it?

Sub TeamA()
Dim TeamRange As Range, TeamNextBlank As Range
Set TeamRange = ActiveSheet.Range("H4:H18")
Set TeamNextBlank = ActiveSheet.Range("H19").End(xlUp)(2)
ActiveCell.Cut Destination:=TeamNextBlank
End Sub

A secondary problem is that if there are no blank cells in the range I need to exit the sub without pasting anything.

 

[Joe4]

xlUp will first the last populated cell in that range. If you want to find the first unpopulated cell in H4:H18, you can go about it something like this:

    Dim myFirstBlankCell As Range
    
    If Range("H4") = "" Then
        Set myFirstBlankCell = Range("H4")
    Else
        Set myFirstBlankCell = Range("H3").End(xlDown).Offset(1, 0)
    End If

 

[samkeenan]

xlUp will first the last populated cell in that range. If you want to find the first unpopulated cell in H4:H18, you can go about it something like this:

    Dim myFirstBlankCell As Range
    
    If Range("H4") = "" Then
        Set myFirstBlankCell = Range("H4")
    Else
        Set myFirstBlankCell = Range("H3").End(xlDown).Offset(1, 0)
    End If

Thanks a lot, that helps! Could I then use something like:

If myFirstBlankCell>("H18") Then End Sub

to exit without pasting if no blank cells are found in range?

 

[Joe4]

Sure, you could do it like this:

If myFirstBlankCell.Row > 18 Then Exit Sub

 

[samkeenan]

Sure, you could do it like this:

If myFirstBlankCell.Row > 18 Then Exit Sub

Thank you Joe4!