We need to quintuple the size of a database sheet. What are the do's and don't's to make this run smoothly?

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
Hi all,

We have a master database of thousands of rows (3,200 so far), each row is a product with the necessary information. We are looking at implementing a new set of products, which will require a new row per product. It's likely we will be adding somewhere between 10,000 to 20,000 rows.


  1. Each row has data up to column AF (32 cols).
  2. There are no live formulas
  3. There is conditional formatting on each row up to column M
  4. The sheet is already somewhat sluggish to use

My question is, how do I stop this from getting unwieldly? Currently my plan is to remove all conditional formatting and replace it with a macro that applies a format (which is usually coloured cells or text) correctly based on the status of the product. I feel like 3,200 cells across 13 columns (41,600 cells) of conditional formatting already makes the sheet sluggish so this is the first step.


Obviously, it's not ideal to keep a database for this purpose in Excel, but these are the tools we have and I have to make the best of the situation. Any more advice given would be appreciated. Thank you!
 
Do you have any code in the sheet modules, or ThisWorkbook module that starts with
Private Sub Worksheet_or Private Sub Workbook_
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Do you have any code in the sheet modules, or ThisWorkbook module that starts with
Private Sub Worksheet_or Private Sub Workbook_

Just these:

VBA Code:
Private Sub Workbook_Open()

If ActiveWorkbook.ReadOnly = False Then
Load StartUp
StartUp.Show
Else
Application.ScreenUpdating = False
Sheets("All products").Activate
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
On Error Resume Next
    If ActiveSheet.AutoFilterMode Then
    ActiveSheet.ShowAllData
    Else
    End If
On Error GoTo 0
End If

Application.ScreenUpdating = True
   
If ErrCount > 0 Then
    If MsgBox("There are " & ErrCount & " products which cannot be reconciled with the 'Cancelled products Master File',  and may need to be manually added.", vbInformation + vbOKOnly, "Unreconciled products") = vbOK Then
    End If
Else
End If

End Sub

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ThisWorkbook.ReadOnly = True Then
Exit Sub
End If

ActiveWorkbook.Save

Dim ex As Workbook, a As Worksheet, tsell As Double
Set a = Worksheets("All products")

Application.ScreenUpdating = False

Set ex = Workbooks.Open("[Redacted]", False, False)

ex.Activate
Cells.ClearContents
Range("A1").Activate
a.Activate
Range("A3").Activate

Do Until Cells(ActiveCell.Row, "A").Value = ""
    If IsNumeric(Cells(ActiveCell.Row, "G").Value) = True And Cells(ActiveCell.Row, "C").Value <> "" Then
        If Len(Cells(ActiveCell.Row, "A").Value) = 6 And Cells(ActiveCell.Row, "G").Value > 0 And DateValue(Cells(ActiveCell.Row, "M")) > Now And Left(Cells(ActiveCell.Row, "A").Value, 1) <> "I" And Left(Cells(ActiveCell.Row, "A").Value, 1) <> "J" Then
        tno = Cells(ActiveCell.Row, "A").Value
        tsell = Cells(ActiveCell.Row, "G").Value
        ex.Activate
        Cells(ActiveCell.Row, "A").Value = tno
        Cells(ActiveCell.Row, "B").Value = tsell
        ActiveCell.Offset(1, 0).Activate
        a.Activate
        End If
    End If
ActiveCell.Offset(1, 0).Activate
Loop

ex.Close True

a.Copy
Range("1:1").Delete xlUp
Set ppcopy = ActiveWorkbook
Application.DisplayAlerts = False
On Error Resume Next
ppcopy.SaveAs "[redacted]", FileFormat:=xlOpenXMLWorkbook
On Error GoTo 0
ppcopy.Close False
Application.DisplayAlerts = True

a.Activate

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Those shouldn't be causing any problems, & 20,000 rows with 32 columns without any formulae shouldn't be a problem either.
So without being able to see the workbook, I don't know what else to suggest.
 
Upvote 0
if you redact it all will you still get the same problem?
If you do, then upload to a share site such as OneDrive, DropBox, mark for sharing & post the link to the thread & I'll have a look.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top