name a range by referencing a cell value

[kdt511]

I'm trying to count the number of rows in column C that have text, and then reference a range of A11:A62 (if 62 was the number of rows that have text). Thanks in advance for any suggestions.

This is working, MATCH(REPT("z",255),C:C), as far as counting the number of rows with text, but it doesn't work for specifying it as a range.


Destination:=Range("A11:INDIRECT("A"&;MATCH(REPT("z",255),C:C))"), Type:=xlFillDefault

[Evagrius]

Since you used "Destination:=" I will assume you are looking for a VB solution. This worked for me - there are easier ways but I wanted to use what you aleady had - it will reference a range in column a based on the last text in column C.

Sub Macro2()
Dim x As String
Dim MyRange As Range

x = Application.WorksheetFunction.Match _
(Application.WorksheetFunction.Rept("z", 255), _
Columns(3))

Set MyRange = Range(Cells(1, 1), Cells(x, 1))

MyRange.Select
End Sub

[kdt511]

Thanks, I'll have to try it when I get to my other computer. I'd be interested in hearing your non VB solution too.

[Evagrius]

To name the range . .

Sub Macro2()
Dim x As String
Dim MyRange As Range


x = Application.WorksheetFunction.Match _
(Application.WorksheetFunction.Rept("z", 255), _
Columns(3))


Set MyRange = Range(Cells(1, 1), Cells(x, 1))
MyRange.Name = "TheList"

End Sub

[royUK]

KDT511
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[kdt511]

That's giving me a few errors, and I'd like to start from scratch.

As simply as possible, I want to have 2 sheets. On sheet 2, there will be a cell with an integer. On sheet 1, I want the macro to do an auto fill from A11 to A(integer). Thanks.

[Evagrius]

My apologies RoyUK, it wasn't my intention to contribute to an improperly posted thread. I will be a bit more careful.

[kdt511]

My apologies as well. I've read the rules thoroughly now. Thanks.