NewOrderFac33
Well-known Member
- Joined
- Sep 26, 2011
- Messages
- 1,275
- Office Version
- 2016
- 2010
- Platform
- Windows
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:
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
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:
Code:
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
Last edited: