Excel Sheet too Large - Need to redefine Range

[CCSlice]

Hi Mr. Excel Nation!

I am working with a large spreadsheet and the truth is that all the columns are not required for what the client wants to be done. I have attached a snip of the spreadsheet here but know that in reality this spreadsheet is about 30 columns and over 10,000 rows. I have highlighted the columns in red that I need in order to carry out the opening and updating of the reports located in SharePoint. The easy answer is to reduced the number of rows that is brought into Excel using Power Query, BUT the client, needs every column to be visible. Can I create an array of the columns I need rather than the whole sheet?

Report_Snip.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Location	Report Name	Business Unit	Link	Status	Priority	Comments	Support	Team Lead	Refresh Cycle	Choice 1	Choice 2	Date Applied	Olink	Elink	Alink	Updated	Updated By	Audience	ID	Count	Path	Checked
2	North Building	abc.xlsx	Marketing	https://sharepoint.com/Marketing/abc.xlsx	Active	Low		Staff	Tom Fielding	Weekly			4/21/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/21/2022 11:45 AM	Tom Fielding		14532	1	CLASSIFIED	Yes
3	Central Building	Employee_Asset.xlsx	Admin	https://sharepoint.com/Admin/Employee_Asset.xlsx	Active	Med		Dept	Suzanne Durrant	Weekly			4/24/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/24/2022 9:30 AM	Michelle Fennings		19452	1	CLASSIFIED	Yes
4	DMZ	End Point Mappings.xlsx	IT	https://sharepoint.com/IT/End Point Mappings.xlsx	Active	High		Dept	Henry Nguyen	Bi-Weekly			4/11/2022	CLASSIFIED	CLASSIFIED	CLASSIFIED	04/11/2022 3:30 PM	Lazar Popovic		12449	1	CLASSIFIED	Yes
5
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=HYPERLINK("https://sharepoint.com/"&C2&"/"&B2)

 

[johnnyL]

Can I create an array of the columns I need rather than the whole sheet?

Certainly!

As an example of what you posted, and assuming the B Column to be the longest column, You could use something like the following to load just certain columns of the sheet into a 2D 1based array:

Sub LoadNoncontiguousColumnsToArray()
'
    Dim DataLastRow             As Long, DataStartRow   As Long
    Dim DesiredInputColumns     As String
    Dim LastRowColumn           As String
    Dim InputArray              As Variant
'
    DataStartRow = 2                                                                    ' <--- Set this to the start row of data
    DesiredInputColumns = "2,4,5,10,11,14,15,16"                                        ' <--- Set the column #s from source sheet to be loaded into array
    LastRowColumn = "B"                                                                 ' <--- Set this to the column letter to use to determine last row
'
    DataLastRow = Range(LastRowColumn & Rows.Count).End(xlUp).Row                       ' Get last row used in Column
'
    InputArray = Application.Index(Cells, Evaluate("ROW(" & DataStartRow & ":" & _
            DataLastRow & ")"), Split(DesiredInputColumns, ","))                        ' Load the designated column #s into 2D 1 based InputArray
End Sub

 

[CCSlice]

Certainly!

As an example of what you posted, and assuming the B Column to be the longest column, You could use something like the following to load just certain columns of the sheet into a 2D 1based array:

Sub LoadNoncontiguousColumnsToArray()
'
    Dim DataLastRow             As Long, DataStartRow   As Long
    Dim DesiredInputColumns     As String
    Dim LastRowColumn           As String
    Dim InputArray              As Variant
'
    DataStartRow = 2                                                                    ' <--- Set this to the start row of data
    DesiredInputColumns = "2,4,5,10,11,14,15,16"                                        ' <--- Set the column #s from source sheet to be loaded into array
    LastRowColumn = "B"                                                                 ' <--- Set this to the column letter to use to determine last row
'
    DataLastRow = Range(LastRowColumn & Rows.Count).End(xlUp).Row                       ' Get last row used in Column
'
    InputArray = Application.Index(Cells, Evaluate("ROW(" & DataStartRow & ":" & _
            DataLastRow & ")"), Split(DesiredInputColumns, ","))                        ' Load the designated column #s into 2D 1 based InputArray
End Sub

This is good. Can the array be filled upon a condition? For example, column E is filled with Inactive and Active reports. No sense in loading inactive reports into the array. So could the array be populated when the report shows as active?

 

[johnnyL]

It would take longer to look through Column E for 'Active' status as opposed to just loading that entire column into the array and then check the status of that column later in your code.

Each time you try to read/write from/to a sheet takes time. It is way way faster to look at the values when they are in an array than when they are in a sheet.

 

[bebo021999]

Why not using UNION function?

Dim U as range
lastrow = ... ' last row of usedrange
U = UNION(range("B2:B" & lastrow), range("D2:D" & lastrow), range("E2:E" & lastrow), range("J2:K" & lastrow, range("N2:P" & lastrow)
'Then using For ... Next loop through each element in U only

 

[CCSlice]

It would take longer to look through Column E for 'Active' status as opposed to just loading that entire column into the array and then check the status of that column later in your code.

Each time you try to read/write from/to a sheet takes time. It is way way faster to look at the values when they are in an array than when they are in a sheet.

The reason why I asked is that the spreadsheet represents several reports from SharePoint locations. We don't have the Power Automate suite of tools, so we are looking to update our reports by opening and refreshing. But I rather have reports that are active to undergo that update than to have all reports that are not even required anymore to be opened and refreshed.

 

[CCSlice]

Why not using UNION function?

Dim U as range
lastrow = ... ' last row of usedrange
U = UNION(range("B2:B" & lastrow), range("D2:D" & lastrow), range("E2:E" & lastrow), range("J2:K" & lastrow, range("N2:P" & lastrow)
'Then using For ... Next loop through each element in U only

This is good. But to get the used range it's LastRow = ActiveSheet("Sheet1").UsedRange.Rows.Count?

 

[johnnyL]

The reason why I asked is that the spreadsheet represents several reports from SharePoint locations. We don't have the Power Automate suite of tools, so we are looking to update our reports by opening and refreshing. But I rather have reports that are active to undergo that update than to have all reports that are not even required anymore to be opened and refreshed.

I mentioned you can check the "Active" status later in the code.

Here is the code that includes that:

Sub LoadNoncontiguousColumnsToArrayV2()
'
    Dim ArrayColumn             As Long, ArrayRow       As Long
    Dim DataLastRow             As Long, DataStartRow   As Long
    Dim RowCount                As Long
    Dim DesiredInputColumns     As String
    Dim LastRowColumn           As String
    Dim ActiveInputArray        As Variant, InputArray  As Variant
'
    DataStartRow = 2                                                                    ' <--- Set this to the start row of data
    DesiredInputColumns = "2,4,5,10,11,14,15,16"                                        ' <--- Set the column #s from source sheet to be loaded into array
    LastRowColumn = "B"                                                                 ' <--- Set this to the column letter to use to determine last row
'
    DataLastRow = Range(LastRowColumn & Rows.Count).End(xlUp).Row                       ' Get last row used in Column
'
    InputArray = Application.Index(Cells, Evaluate("ROW(" & DataStartRow & ":" & _
            DataLastRow & ")"), Split(DesiredInputColumns, ","))                        ' Load the designated column #s into 2D 1 based InputArray
'
    ReDim ActiveInputArray(1 To UBound(InputArray, 1), 1 To UBound(InputArray, 2))      ' Set # of rows & Columns of ActiveInputArray same as InputArray
'
    RowCount = 0                                                                        ' Initialize RowCount
'
    For ArrayRow = 1 To UBound(InputArray, 1)                                           ' Loop through the rows of InputArray
        If Trim(InputArray(ArrayRow, 3)) = "Active" Then                                '   If 3rd column in InputArray = "Active" Then ...
            RowCount = RowCount + 1                                                     '       Increment RowCount
'
            For ArrayColumn = 1 To UBound(InputArray, 2)                                '       Loop through columns of InputArray
                ActiveInputArray(RowCount, ArrayColumn) = InputArray(ArrayRow, ArrayColumn) '       Save row data from InputArray to ActiveInputArray
            Next                                                                        '       Loop back
        End If
    Next                                                                                ' Loop back
'
' *** ActiveInputArray now contains only the 'Active' rows of data ***
'
End Sub

It is done in memory to be be faster than continuously looking at the cells in the sheet.