Hybrid between Cells.Select and ActiveCell.CurrentRegion?  
Author Message
magicalclick





PostPosted: Visual Basic for Applications (VBA), Hybrid between Cells.Select and ActiveCell.CurrentRegion? Top

Hello guys,

Assume I have data like this. Each < > Means a cell. < > Means nothing in the cell.

<A1><B1>

< >< >

< ><B3>

If I use Cells.Select, the range will be ("A1: IV65536") too much selection.

If I use ActiveCell.CurrentRegion, the range will be ("A1:B1") not enought selection.

I wish to select the range ("A1:B3"). What should I do

Thank you.



Microsoft ISV Community Center Forums3  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Hybrid between Cells.Select and ActiveCell.CurrentRegion? Top

Here's code that demonstrates how to do this.

Option Explicit

Public Sub DoSelect()
Dim c1 As String, c2 As String
With Sheet1
c1 = .Cells(1, 1)
c2 = .Cells(2, 1)
.Range(c1 & ":" & c2).Select
End With
End Sub

It takes the value in cell A1 and the value in cell B1 and selects the range described by those two values. If you type
"C1" in cell A1 and "D8" in cell B1 and then run this routine, the range "C1:D8" will be selected. You should be able to easilyt modify this to read the cell addresses from your own data structure rather than from cells on the worksheet.



 
 
magicalclick





PostPosted: Visual Basic for Applications (VBA), Hybrid between Cells.Select and ActiveCell.CurrentRegion? Top

Edit: Hello NVM. I found a way to do it. I use previous search on * from the last cell. Why didn't I thought of that before, silly me.

 

 

Thanks for the reply, but I don't know the boundary of the data. I can't specify it. The data is exported from a program that I have no control of. Mostly I want to find the lowest row of the data, but I don't know which column will give me the last row. Any sugguestions Thank you.