Run-time error 1004:Method 'Range' of object'_Global' failed

[erimhast]

Hello,
I am trying to run the below code to find all blank cells in Columns G and H and to clear the contents so my other code to fill those blank cells with data from Columns J and K will work. The way i found that this would work is to do a find for the blank rows in the columns, select them all and clear contents. I recorded this and the below code is what came from that however i get a run time error every time i try to run it while stepping through the macro. Any thoughts? Thank you in advance!






Columns("G:H").Select
ActiveCell.FormulaR1C1 = ""
Union(Range( _
"G1008:G1014,G1016:G1018,G1030:G1033,G1047:G1048,G1050:G1052,G1056,H922:H1062,G1216:G1219,G1253:G1256,G1442,G1446:G1450,G1464:G1470,G1544:G1545,G1555:G1559,G1629:G1655,G1659:G1668,G1676:G1683,G1691:G1693,G1699,G1701:G1707,G1713:G1717,G1721:G1723,G1727:G1729" _
), Range( _
"G1976,G2023:G2025,G2028:G2030,G2042,G2044,G2047:G2048,G2050,G2052,G2056,G2058:G2061,G2067:G2068,G2077:G2078,G2080:G2123,G2170:G2197,G2212:G2217,G2219,G2313:G2314,G2322,G2326,G2329,G2335:G2338,G2341:G2342,G2346,G2350,G2354:G2356,G2359:G2377,G2380:G2382,G2386" _
), Range( _
"G2444,G2446:G2448,G2481:G2496,G2513:G2537,G2543:G2544,G2563:G2565,G2568:G2569,G2578,G2600,G2604,G2607:G2619,G2624:G2627,G2654:G2659,G2662,G2678:G2702,G2704,G2707:G2709,G2735:G2740,G2743:G2744,G2751:G2761,G2767:G2770,G2773,G2780:G2799,G2808,G2875:G2884" _
), Range( _
"G3005:G3007,G3015:G3017,G3060:G3065,G3107:G3108,G3138:G3142,G3214:G3216,G3335,G3407:G3418,G3438:G3463,G3467,G3502:G3505,G3509:G3510,G3514:G3519,G3530:G3532,G3545:G3554,G3563:G3565,H1216:H3568,G3569:H3569,G3571:H3639,G3641:G3816,G3818,H3641:H3821,G3822:H4347" _
), Range( _
"H177,G316:G328,G360:G380,G387,G397:G445,G448,H316:H449,G450:H452,G457:G458,G462,G464:G484,G500:G581,G588:G598,G600:G617,G631:G634,H457:H656,G657:H661,G844:H847,G932:G933,G936:G945,G947:G948,G951:G953,G956:G957,G960,G979:G980,G983,G987,G990:G994,G999:G1002" _
)).Select
Selection.ClearContents

 

[Dendro]

clearing contents of blank cells...aren't blanks cells empty? how do you make something empty more empty?

 

[erimhast]

They appear empty but if you use a macro to find the blank cells and replace with other data, those "blank" cells get overlooked. I am trying to clear out whatever it is in the cells but isn't visible.

 

[Dendro]

make a for loop going from row one to the last row, use isempty under an if function to check for empty cells and clear it. But maybe the problem is not that they aren't empty, the problem might occur due to the second macro? could you post this macro?

 

[erimhast]

make a for loop going from row one to the last row, use isempty under an if function to check for empty cells and clear it. But maybe the problem is not that they aren't empty, the problem might occur due to the second macro? could you post this macro?

on Error Resume Next
With Columns("G").SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=RC[+3]"
.Value = .Value
End With

 

[Dendro]

Seems ok, but why the on error resume next, we could use the error to find where the problem occurs...
Don't think it makes a difference but i never write with [+3] just [3]

 

[erimhast]

I am getting the run time error on the first portion only, i was originally only using the second portion and it is working fine on other worksheets within my workbook but the "blank" cells weren't being recognized on a different worksheet.

 

[erimhast]

I know you explained what i should do to correct the first portion of the macro but could you help me with a little code? I am pretty new to writing macros myself and it has been a long week. Thanks in advance!!!

 

[Dendro]

I'm not an expert either, but I'm willing to help out. It's been a long day so I'll take a look at it tomorrow. PM me if I would forget

 

[erimhast]

I won't be working tomorrow but Monday I will be sure to send you a message.