SOLVED

Excel Macro Error Error 1004 : Range Method Global ERROR

%3CLINGO-SUB%20id%3D%22lingo-sub-2073357%22%20slang%3D%22en-US%22%3EExcel%20Macro%20Error%20Error%201004%20%3A%20Range%20Method%20Global%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22StevenChailes_0-1611045706920.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247394iA49095C522121AF3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22StevenChailes_0-1611045706920.png%22%20alt%3D%22StevenChailes_0-1611045706920.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERange(%22B1%3AU18%2CW1%3AAL18%2CAN1%3ABE18%2CBF1%3ABW18%2CBY1%3ACU18%2CCW1%3ADJ18%2CDL1%3AEE18%2CEF1%3AEY18%2CFA1%3AFV18%2CFW1%3AGR18%2CGT1%3AHM18%2CHN1%3AIG18%2CII1%3AJB18%2CJC1%3AJV18%2CJX1%3AKT18%2CKU1%3ALQ18%2CLS1%3AMO18%2CMP1%3ANL18%2CNN1%3AOG18%2COH1%3APA18%2CPC1%3APV18%2CPW1%3AQP18%2CQR1%3ARK18%2CRM1%3ASG18%2CSI1%3ATC18%2CTE1%3ATY18%2CUA1%3AUR18%2CUS1%3AVJ18%2CVK1%3AWB18%2CWD1%3AWS18%2CWT1%3AXI18%2CXJ1%3AXY18%2CYA1%3AYQ18%2CYS1%3AZI18%2CZK1%3AAAA18%22).Select%3CBR%20%2F%3EDim%20invoiceRng%20As%20Range%3CBR%20%2F%3ESet%20invoiceRng%20%3D%20Range(%22B1%3AU18%2CW1%3AAL18%2CAN1%3ABE18%2CBF1%3ABW18%2CBY1%3ACU18%2CCW1%3ADJ18%2CDL1%3AEE18%2CEF1%3AEY18%2CFA1%3AFV18%2CFW1%3AGR18%2CGT1%3AHM18%2CHN1%3AIG18%2CII1%3AJB18%2CJC1%3AJV18%2CJX1%3AKT18%2CKU1%3ALQ18%2CLS1%3AMO18%2CMP1%3ANL18%2CNN1%3AOG18%2COH1%3APA18%2CPC1%3APV18%2CPW1%3AQP18%2CQR1%3ARK18%2CRM1%3ASG18%2CSI1%3ATC18%2CTE1%3ATY18%2CUA1%3AUR18%2CUS1%3AVJ18%2CVK1%3AWB18%2CWD1%3AWS18%2CWT1%3AXI18%2CXJ1%3AXY18%2CYA1%3AYQ18%2CYS1%3AZI18%2CZK1%3AAAA18%22)%3CBR%20%2F%3EinvoiceRng.ExportAsFixedFormat%20_%3CBR%20%2F%3EType%3A%3DxlTypePDF%2C%20_%3CBR%20%2F%3EFilename%3A%3D%22C%3A%5CUsers%5CUSER%5CDesktop%5CG01.pdf%22%2C%20_%3CBR%20%2F%3EQuality%3A%3DxlQualityStandard%2C%20_%3CBR%20%2F%3EIncludeDocProperties%3A%3DTrue%2C%20_%3CBR%20%2F%3EIgnorePrintAreas%3A%3DFalse%2C%20_%3CBR%20%2F%3EOpenAfterPublish%3A%3DFalse%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2073357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2073573%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%20Error%20Error%201004%20%3A%20Range%20Method%20Global%20ERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073573%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934172%22%20target%3D%22_blank%22%3E%40StevenChailes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20like%20you've%20hit%20the%20limit%20of%20256%20characters%20in%20the%20address%20(it%20breaks%20after%26nbsp%3BUS1%3AVJ18%20).%3CBR%20%2F%3E%3CBR%20%2F%3EWriting%20Macros%20this%20way%20isn't%20too%20safe%2C%26nbsp%3B%20you%20should%20try%20to%20use%20Named%20Ranges%20wherever%20possible%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20you%20could%20split%20it%20into%20chunks%20and%20do%26nbsp%3BRange(%22Range1%2CRange2%22).Select%3CBR%20%2F%3E%3CBR%20%2F%3EWyn%3C%2FP%3E%0A%3CP%3EMVP%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

StevenChailes_0-1611045706920.png

Range("B1:U18,W1:AL18,AN1:BE18,BF1:BW18,BY1:CU18,CW1:DJ18,DL1:EE18,EF1:EY18,FA1:FV18,FW1:GR18,GT1:HM18,HN1:IG18,II1:JB18,JC1:JV18,JX1:KT18,KU1:LQ18,LS1:MO18,MP1:NL18,NN1:OG18,OH1:PA18,PC1:PV18,PW1:QP18,QR1:RK18,RM1:SG18,SI1:TC18,TE1:TY18,UA1:UR18,US1:VJ18,VK1:WB18,WD1:WS18,WT1:XI18,XJ1:XY18,YA1:YQ18,YS1:ZI18,ZK1:AAA18").Select
Dim invoiceRng As Range
Set invoiceRng = Range("B1:U18,W1:AL18,AN1:BE18,BF1:BW18,BY1:CU18,CW1:DJ18,DL1:EE18,EF1:EY18,FA1:FV18,FW1:GR18,GT1:HM18,HN1:IG18,II1:JB18,JC1:JV18,JX1:KT18,KU1:LQ18,LS1:MO18,MP1:NL18,NN1:OG18,OH1:PA18,PC1:PV18,PW1:QP18,QR1:RK18,RM1:SG18,SI1:TC18,TE1:TY18,UA1:UR18,US1:VJ18,VK1:WB18,WD1:WS18,WT1:XI18,XJ1:XY18,YA1:YQ18,YS1:ZI18,ZK1:AAA18")
invoiceRng.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Users\USER\Desktop\G01.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

3 Replies

Hi @StevenChailes 

 

Looks like you've hit the limit of 256 characters in the address (it breaks after US1:VJ18 ).

Writing Macros this way isn't too safe,  you should try to use Named Ranges wherever possible

 

Then you could split it into chunks and do Range("Range1,Range2").Select

Wyn

MVP

Best Response confirmed by StevenChailes (New Contributor)
Solution
I believe you could do this, at least until you find a better method. Personally, I also favor using named ranges.

Union(Range("B1:U18,W1:AL18,AN1:BE18,BF1:BW18,BY1:CU18,CW1:DJ18,DL1:EE18,EF1:EY18,FA1:FV18,FW1:GR18,GT1:HM18,HN1:IG18,II1:JB18,JC1:JV18,JX1:KT18,KU1:LQ18,LS1:MO18,MP1:NL18"), Range("NN1:OG18,OH1:PA18,PC1:PV18,PW1:QP18,QR1:RK18,RM1:SG18,SI1:TC18,TE1:TY18,UA1:UR18,US1:VJ18,VK1:WB18,WD1:WS18,WT1:XI18,XJ1:XY18,YA1:YQ18,YS1:ZI18,ZK1:AAA18")).Select

Thnaks bro, it works. @JMB17