Cell clear function will not work with hidden rows

[dshervin1]

I have the following VBA code in my sheet:

Sub Clear_Contents()


Range("b12:b45,b51:b78,b84:b92,b97:b105,d12:d45,d51:d78,d84:d92,d97:d105,e12:e45,e51:e78,e84:e92,e97:e105,f12:f45,f51:f78,f84:f92,f97:f105,g12:g45,g51:g78,g84:g92,g97:g105,h12:h45,h51:h78,h84:h92,h97:h105,i12:i45,i51:i78,i84:i92,i97:i105,k12:k45,k51:k78,k84:k92,k97:k105,m12:m45,m51:m78,m84:m92,m97:m105,n12:n45,n51:n78,n84:n92,n97:n105,o1245,o5178,o8492,o97105,p1245,p5178,p8492,p97105,q12:q45,q51:q78,q84:q92,q97:q105,r12:r45,r51:r78,r84:r92,r97:r105,t12:t45,t51:t78,t84:t92,t97:t105").ClearContents


End Sub

I am getting an error code 400, and I believe it is because some of the cells included in this mixture are in hidden rows, Is there anyway to fix this so I can run this and it will still clear all the cells? If the rows are hidden, then the cells do not have to be cleared if this helps at all.

 

[Hashiru]

<code style="margin: 0px; padding: 0px; font-size: 1em; font-family: Monaco, 'Courier New', monospace;">Hi Dshervin try this one

Sub Clear_Contents()

<code style="margin: 0px; padding: 0px; font-size: 1em; font-family: Monaco, 'Courier New', monospace;">Dim RangesToDelete As String
RangesToDelete =(b12:b45,b51:b78,b84:b92,b97:b105,d12:d45,d51:d78,d84:d92,d97:d105,e12:e45,e51:e78,e84:e92,e97:e105,f12:f45,f51:f78,f84:f 92,f97:f105,g12:g45,g51:g78,g84:g92,g97:g105,h12:h45,h51:h78,h84:h92,h97:h105,i12:i45,i51:i78,i84:i92,i97:i105,k12:k45,k 51:k78,k84:k92,k97:k105,m12:m45,m51:m78,m84:m92,m97:m105,n12:n45,n51:n78,n84:n92,n97:n105,o1245,o5178,o8492,o971 05,p1245,p5178,p8492,p97105,q12:q45,q51:q78,q84:q92,q97:q105,r12:r45,r51:r78,r84:r92,r97:r105,t12:t45,t51:t78,t8 4:t92,t97:t105)

For Each ws In ThisWorkbook.Worksheets

With ws .AutoFilterMode = False
.Range(RangesToDelete).EntireRow.Hidden = False .Range(RangesToDelete).EntireColumn.Hidden = False
.Range(RangesToDelete).ClearContents 'This deletes only cell contents
'To deletes cell contents and formatting
'.Range(RangesToDelete).Clear
End With
Next ws</code>

End Sub</code>

 

[dshervin1]

It still says "Compile Error: Syntax Error." Then it highlights this range: RangesToDelete =(b12:b45,b51:b78,b84:b92,b97:b105,d12:d45,d51:d78,d84:d92,d97:d105,e12:e45,e51:e78,e84:e92,e97:e105,f12:f45,f51:f78,f84:f 92,f97:f105,g12:g45,g51:g78,g84:g92,g97:g105,h12:h45,h51:h78,h84:h92,h97:h105,i12:i45,i51:i78,i84:i92,i97:i105,k12:k45,k 51:k78,k84:k92,k97:k105,m12:m45,m51:m78,m84:m92,m97:m105,n12:n45,n51:n78,n84:n92,n97:n105,o1245,o5178,o8492,o971 05,p1245,p5178,p8492,p97105,q12:q45,q51:q78,q84:q92,q97:q105,r12:r45,r51:r78,r84:r92,r97:r105,t12:t45,t51:t78,t8 4:t92,t97:t105)

 

[mikerickson]

I suspect a typo in that monster of a cell address.
This works for me

With Range("12:45, 51:78, 84:92, 97:105")
    With Application.Intersect(.EntireRow, Range("B:B, D:I, K:R, T:T"))
        .ClearContents
    End With
End With

 

[dshervin1]

Thanks so much to both of you!!! mikeerickson's worked perfectly! I think there was an error in that monster of a cell address, didnt know that you could shorten it like that!!!

 

[Hashiru]

Hi Dshervin try this one sorry for the typo.

Sub Clear_Contents()

<code style="margin: 0px; padding: 0px; font-family: Monaco, 'Courier New', monospace; line-height: 13px; color: rgb(51, 51, 51); font-size: 1em; background-color: rgb(250, 250, 250);">Dim RangesToDelete As String
RangesToDelete =("b12:b45,b51:b78,b84:b92,b97:b105,d12:d45,d51:d78,d84:d92,d97:d105,e12:e45,e51:e78,e84:e92,e97:e105,f12:f45,f51:f78,f84:f 92,f97:f105,g12:g45,g51:g78,g84:g92,g97:g105,h12:h45,h51:h78,h84:h92,h97:h105,i12:i45,i51:i78,i84:i92,i97:i105,k12:k45,k 51:k78,k84:k92,k97:k105,m12:m45,m51:m78,m84:m92,m97:m105,n12:n45,n51:n78,n84:n92,n97:n105,o1245,o5178,o8492,o971 05,p1245,p5178,p8492,p97105,q12:q45,q51:q78,q84:q92,q97:q105,r12:r45,r51:r78,r84:r92,r97:r105,t12:t45,t51:t78,t8 4:t92,t97:t105")

For Each ws In ThisWorkbook.Worksheets

With ws .AutoFilterMode = False
.Range(RangesToDelete).EntireRow.Hidden = False .Range(RangesToDelete).EntireColumn.Hidden = False
.Range(RangesToDelete).ClearContents 'This deletes only cell contents
'To deletes cell contents and formatting
'.Range(RangesToDelete).Clear
End With
Next ws</code>

End Sub

I do not check your ranges and the problem must lie there is the code worked for me. Sorry I had typo problems just now. No quote for my ranges "".

I suggest you check it.