Vba: defining a range with reference A1

[Nelson78]

Hello everybody.

I need to define a range from A1 (top left of the range) to the last populated cell on the right, and to the last populate row downward.

https://imgur.com/a/keBiXeZ

In this case, the range has to be A1:D21.

Consider other cells in other areas could be populated.

Thank you.

 

[nemmi69]

something like this?

Dim LastRowNo As Long
Dim LastColNo As Long


Sub FindLastColRow()
LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LastColNo = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column
MsgBox "Last Row " & LastRowNo & " | Last Column " & LastColNo


End Sub

 

[MARK858]

Based on you image as assuming you want the highlighted area...

Sub CrEG()
Range("A1").CurrentRegion.Select
End Sub

 

[Nelson78]

Based on you image as assuming you want the highlighted area...

Sub CrEG()
Range("A1").CurrentRegion.Select
End Sub

Something like this seems to work (without the use of "select").

Dim range1 As Range
Set range1 = sheet5.Range("A1").CurrentRegion

 

[MARK858]

You don't need Select, I only used it because you weren't clear on what you wanted to do with it

I could have used the below just as easily...

Sub CrEG()
MsgBox Range("A1").CurrentRegion.Address
End Sub

 

[nikio8]

Using current region you may get unexpected results. It is better if you have formula to find the edges as suggested previously.


From Mr Excel book, hehe

Sub FillIn()
On Error Resume Next
'Need this because if there aren't any blank 'cells, the code will error
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "empty"

Range("A1").CurrentRegion.Interior.Color = vbyellow
End Sub

 

[MARK858]

You shouldn't have unexpected results with currentregion as what makes up the currentregion is clearly defined in the help files. It is just learning what currentregion actually means
As stated I used currentregion based on the image supplied by the OP.

 

[Fluff]

Using current region you may get unexpected results. It is better if you have formula to find the edges as suggested previously.

Given the image & description supplied by the OP, if you look for the last used row/column you'll end up with a range A1:J33, which is not what the OP wanted.
Whereas CurrentRegion will give A1:D21 as per the OPs request

 

[MARK858]

if you have formula to find the edges as suggested previously.

@nikio8, just to elaborate (as I am now I am home from work) the only previous suggestion wasn't to use a formula. It was to use Rows.count xlup and Columns.Count xlleft which as per the image would work but...

it wouldn't cover for the OP's statement which was

Consider other cells in other areas could be populated.

so if you put anything if in row 2 from column F onwards or column A from row 23 onwards then it won't give the result the OP requested.

The idea of filling every blank in the current region with the word "empty" which is what

Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _ 
= "empty"

does, I would never do unless you done a replace afterwards and but even then it wouldn't adjust the currentregion as you are only filling the blanks in what already was the currentregion.

Hope that is clearer.

 

[nemmi69]

Just a quick assumption that at least column A and row 1 will have something in each cell you could use

Sub RevFindLastRowCol()
LastRowNo = Worksheets("Sheet1").Range("A1").End(xlDown).Row
LastColNo = Worksheets("Sheet1").Range("A1").End(xlToRight).Column
MsgBox "Last Row " & LastRowNo & " | Last Column " & LastColNo


End Sub