Define VBA range using "counta"

[Brian F]

Excel 2010, I have this VBA routine that sets row heights on the basis of some parameters that begins like this;

Sub setHeights()

Dim targetRange As Range
Dim targetCell As Range

Set targetRange = Range("T18:T53")

For Each targetCell In targetRange.Cells
If Not IsEmpty(targetCell.Value) Then
If targetCell.Value = "Photo Comment: " Then
targetCell.RowHeight = 185 'and so forth

But instead of "Set targetRange = Range("T18:T53")" the range I really want would be something like "Set targetRange = Range (T8:T&counta(AD18:AD100)+15)"

In other words I want the range to be from T8 to T&(count the non empty cells in the range AD18:AD100 and add 15)

Can some one help me say that in VBA?

 

[shg]

You pretty much had it:

Set targetRange = Range("T8:T" & WorksheetFunction.CountA(Range("AD18:AD100").Value) + 15)

 

[Brian F]

Thank you so much that worked perfectly!

 

[shg]

You're welcome, good luck.

 

[bg3075]

I have a very similar module to Brian's. It was created from a macro recording, which copies three columns from 'sheet1' and pastes into three columns of 'sheet2'. Currently I had it select enough rows in 'sheet1' to cover any amount of data we will likely input into it, but would rather use the COUNTA function to select to the end of list in 'sheet1'. Can you help me modify please?

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Sheet1").Select
    Range("A4:B298").Select
    ActiveWindow.SmallScroll Down:=-63
    ActiveWindow.LargeScroll Down:=-5
    Range("A4:B298,F4:F298").Select
    Range("F4").Activate
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste

 

[bg3075]

OK, I got it after a little trial and error.

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Sheet1").Select
    Range("A4", "B4").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A2").Select
        ActiveSheet.Paste
        Sheets("Sheet1").Select
    Range("F4").Select
        Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Sheet2").Select
    Range("D2").Select
    ActiveSheet.Paste