Defining Print_Area based on last row with a value, not just formatting.

[NewOrderFac33]

Good afternoon,

I have a worksheet that will contain data between columns A and AG

Data will only be found in alternate columns B,D,F to AF etc) e.g there might by 5 entries in B, 2 in D, 8 in E and so on.

I want the Print_Area of the worksheet to extend to column AG and as far down as the last value in any of these columns.

I can't use CurrentRegion as each of the columns containing data are separated by blank columns C, E, G through to AE

A further complication is that I only want to extend downwards to the last row that that contain a VALUE in any of these columns, not cells that are merely formatted - I started to use the following:

Dim LastRow As Long
    Dim LastColumn As Long
    
    With ActiveSheet.UsedRange
        LastRow = .Rows(.Rows.Count).Row
        LastColumn = .Columns(.Columns.Count).Column
    End With
    MsgBox ("Last Row: " & LastRow & Chr(10) & "Last Column: " & LastColumn)
    Range("A1").Offset(0, 0).Resize(LastRow, LastColumn).Name = "Print_Area"

My last value was in row 32, but rows 33-35 had a fill colour and LastRow returned 35, not 32.

I don't want to delete the blank columns for presentation purposes.

Can anyone suggest how this could be achieved, please?

Regards

Pete

 

[JoeMo]

This will find the last row with formulas in it even if the formulas are returning "".

LastRow=.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If you change the argument in red to xlValues it will find the last row with a value in it. Find the last column analogs by changing the searchorder argument to xlByColumns.

 

[NewOrderFac33]

It's 21.45 here and my workbook is at work (where it belongs!), so I'm just logging in to say thank you for your solution - I'll give it a try tomorrow! Pete

 

[NewOrderFac33]

This worked a treat - thank you very much, and apologies for the late reply!

 

[JoeMo]

This worked a treat - thank you very much, and apologies for the late reply!

You are welcome - thanks for the reply.