Excel VBA out of memory error with simple line of code

Question

Getting an Out Of Memory Error with excel VBA with a simple line of code that has always worked until now.

    Dim lRow, lCol, rowCount, totalRows As Long
Dim tblRng, vLookup, vLookup2, vLookup3 As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim branch, position, branchName, seniorLu, region, branchNum As Variant

Range("B2").Cut Range("C2")
Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

lRow = Cells(Rows.Count, 1).End(xlUp).Row '<---- this line is the error
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Not sure why this seemingly simple line of code would be throwing the out of memory error. It has worked every week for over a year until just now.

Any thoughts are greatly appreciated

Dim lRow As Long, lCol As Long, rowCount As Long, totalRows As Long. You need to repeat the type for each variable. Though I'm surprised you're getting an Out of Memory error for an implicit Variant. Something else may be going on. — BigBen
– BigBen, Commented Jun 16, 2021 at 13:50
Just in case: Did you turn it off and on again (Aka: Did you restart your computer)? — Pᴇʜ
– Pᴇʜ, Commented Jun 16, 2021 at 13:55
@Pᴇʜ yeah i've restarted my main computer twice, even tried it on another computer. it's strange that it just started happening right now. — Scott Taylor
– Scott Taylor, Commented Jun 16, 2021 at 14:07
Do you have any Add-Ins running? Try disabling them if so (File > Options > Add-Ins > Manage COM Addins) — Absinthe
– Absinthe, Commented Jun 16, 2021 at 14:09

Pᴇʜ · Accepted Answer · 2021-06-16 14:11:49Z

Just a digg into the dark, but I recommend to get your variables declared properly and specify the workbook/worksheet for your Range, Cells, Rows and Columns objects properly and see if it helps:

Dim lRow As Long, lCol As Long, rowCount As Long, totalRows As Long
Dim tblRng As Range, vLookup As Range, vLookup2 As Range, vLookup3 As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim branch, position, branchName, seniorLu, region, branchNum As Variant

Set wb = ThisWorkbook ' ‹-- define your workbook
Set ws = wb.Worksheets("Sheet1") ' ‹-- define your sheet

' use that ws as worksheet
ws.Range("B2").Cut ws.Range("C2")
ws.Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 
lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Always make sure there is no Range, Cells, Rows and Columns object without a fully specified workbook/worksheet.

BigBen · Accepted Answer · 2021-06-16 17:30:30Z

0

Repeat the variable type for each variable:

Dim lRow As Long, lCol As Long, rowCount As Long, totalRows As Long

edited Jun 16, 2021 at 17:30

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

answered Jun 16, 2021 at 14:11

Scott Taylor

1032 silver badges11 bronze badges

Collectives™ on Stack Overflow

Excel VBA out of memory error with simple line of code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related