Applying function to Range

[bjcowen9000]

Hello, I want to apply this function to a range that I do not have to individually highlight on individual sheets. Can someone edit my code to make the range be columns W:AF but only going down to the last row in which there is data? So you could check column A for instance, to see if there is data. And the last row that column A has data, the last row the function will go through on W:AF. I would also like it to work over every worksheet.

Function GetCells(MyRange As Range)
Dim Cell As Range
For Each Cell In MyRange
    If Cell <> "" Then
        GetCells = GetCells & Cell.Value & " "
    End If
Next Cell
If GetCells <> "" Then GetCells = Left(GetCells, Len(GetCells) - 1)
End Function
Sub Concatenate()

End Sub

I think I could use something like

LR = Cells(Rows.Count, "A").End(xlUp).Row
set MyRange=("W1:AF" & LR)

but I am not sure because everything I have tried is not working.

[arlu1201]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread. http://www.excelforum.com/excel-prog...to-ranges.html

Thread Closed.