Dynamic selection

[Eurekaonide]

Hi All

I have a range that I want to select it starts in column J3 and goes out to column CO. I want it to select it to the bottom and I have tried various things but its not working please help.

I had this but it might go beyond row 33 at some stage but it does work
Set rng = Range("J3:CO33")

I tried this and it didnt like it
Set rng = Range("J3:CO3").Select
Range(Selection, Selection.End(xlDown)).Select

Thanks in advance

 

[dreid1011]

What module is your code in?

 

[Eurekaonide]

What module is your code in?

Not sure what you mean? its in Excel 365 via desktop app and Module2 in the VBE ?

 

[dreid1011]

Not sure what you mean? its in Excel 365 via desktop app and Module2 in the VBE ?

I mean this, look at this image and tell me where your code is. The sample you see in this image is currently in the "Sheet3 (Sheet3)" module, which is a worksheet module. The other modules are Workbook (ThisWorkbook) or standard (Module 1).

 

[dreid1011]

Not sure what you mean? its in Excel 365 via desktop app and Module2 in the VBE ?

Okay, since it's in Module 2, you need to identify the worksheet whose range you want to select. Module 2 is not tied to any specific worksheet, so just using Range("J3:CO33") doesn't tell it which worksheet to use.

You need do something like this to identfy the worksheet:

Set rng = Sheets("Sheet2").Range("J3:CO33")

 

[Eurekaonide]

Okay, since it's in Module 2, you need to identify the worksheet whose range you want to select. Module 2 is not tied to any specific worksheet, so just using Range("J3:CO33") doesn't tell it which worksheet to use.

You need do something like this to identfy the worksheet:

Set rng = Sheets("Sheet2").Range("J3:CO33")

Thanks yes I have already identified the Worksheet its just the range I am struggling with

Heres the first half of the code but its all working accept for the range

I have tried here to specify the last row and column which it does find but then breaks at the set Rng again

Sub SPC_Auths()

Sheets("Gannt (4)").Select

Application.ScreenUpdating = True


Lrow = Range("J3").End(xlDown).Row
LColumn = Range("J3").End(xlToRight).Column

Set Rng = Range(Lrow, LColumn)

For Each cell In Rng.Cells

If cell.Value = 2 Then
'cell.FormulaR1C1 = "k"
cell.Font.Size = 22
cell.Font.Name = "Caiibri"
cell.Font.Color = -6279056 'Purple
cell.Font.Bold = False

 

[Eurekaonide]

Okay, since it's in Module 2, you need to identify the worksheet whose range you want to select. Module 2 is not tied to any specific worksheet, so just using Range("J3:CO33") doesn't tell it which worksheet to use.

You need do something like this to identfy the worksheet:

Set rng = Sheets("Sheet2").Range("J3:CO33")

Yed I had the range set like that but I want it to be more dynamic as it could be longer or shorter

 

[dreid1011]

Will column J always have data in it to the last row?

I would change the code like this:

Lrow = Range("J3:J" & Rows.Count).End(xlUp).Row
LColumn = Range("J:"& Columns.Count).End(xlToRight).Column

Set Rng = Range(Cells(3,12),Cells(LColumn,Lrow))

 

[dreid1011]

Will column J always have data in it to the last row?

I would change the code like this:

Lrow = Range("J3:J" & Rows.Count).End(xlUp).Row
LColumn = Range("J:"& Columns.Count).End(xlToRight).Column

Set Rng = Range(Cells(3,12),Cells(LColumn,Lrow))

Nvm, that's not working yet.

 

[Eurekaonide]

Thanks I finally resolved it like this:


Sheets("Gannt (4)").Select

Application.ScreenUpdating = True

Dim Lastrow As Long
Lastrow = ActiveSheet.Range("J3").CurrentRegion.Rows.Count

Dim Rng As Range


Set Rng = Range("J3:CO" & Lastrow)