Counting cells starting at an Active cell

[MercSKelley]

I need to set an active cell for a loop function. The problem is that the cell I need to start with can vary depending on the output of data from a database. For example the output starts at cell G44. From G44 there can be from 0 to 100 cells of data. The only data that I need is the last 10 cells. I have it functioning if there are at least 10 cells of data from G44 with an End(xlToRight) statement. I then offset my activcell (0,-9). The problem is when there is only 1 to 9 cells worth of data, it offsets back past G44. Is there a simple if then statement or something I can use to when there are less than 10 cells of data. Thanks

 

[SkipVought]

Hi,

Check out How can I rename a table as it changes size faq68-1331

the OFFSET worksheet function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[vbap]

Trying modifying this routine to better suite your needs. Take note that the .End(xltoright) will only go as far right to first empty cell. If you have that case of an empty cell between two cell with data, you'll have to take a different approach to get the last column or last cell.

Sub tst()
Dim rg As Range
Dim ws As Worksheet

Set ws = ActiveSheet
With ws
Set rg = .Range(.Cells(1, 4), .Cells(1, 4).End(xlToRight))
r = rg.Rows.Count
c = rg.Columns.Count
If r * c < 4 Then
MsgBox "less then 4 cells"
Else
MsgBox "greater then or equal to 4 cells"
End If
End With

End Sub

Also lookup Specialcells Method. You can obtain the last cell of a range using the Method.