Delete all data on sheet except certain range

[mokhtar]

Hi everyone

How do you write the codes to delete everything

on sheet1 except certain ranges ( A1: B1 , F1: G1 , K1 , M1 , P1 ) without protection this ranges?

I wonder whether someone could help me please.
Thanks in advance

[Yongle]

One suggestion - assign cell values to be retained to variables, then clear the entire sheet, then re-instate the values to the cells.
you could try something like:



dim a1, b1, f1, g1, k1, m1, p1


With ActiveSheet
a1 = .range("A1").Value
b1 = .range("B1").Value
'etc
.Cells.Clear
.range("A1").Value = a1
.range("B1").Value = b1
'etc

[mokhtar]

Thanks a lot Mr. Yongle for this great idea
Thanks for your solution and it works

please Mr. Yongle see another solution :
http://www.vbaexpress.com/forum/showthread.php?37078-Delete-everything-on-sheet-except

Dim ArrayOfRanges As Variant
Dim ArrayOfValues As Variant
Dim i As Long

With ThisWorkbook.Sheets("Sheet1"): Rem adjust
ArrayOfRanges = Array(.Range("A1"), .Range("B4"):Rem adjust, use continous ranges only
ReDim ArrayOfValues(0 To UBound(ArrayOfRanges))

For i = 0 To UBound(ArrayOfRanges)
ArrayOfValues(i) = ArrayOfRanges(i).Value
Next i

.UsedRange.Clear

For i = 0 To UBound(ArrayOfRanges)
ArrayOfRanges(i).Value = ArrayOfValues(i)
Next i
End With




I'm trying to use this code but i can't and give me error

what i do to use this code ?

Thanks in advance for all your help!!

[Yongle]

The alternative code that you found does the same thing as my suggestion, except that it captures the values of the cells to be retained in an array. (My suggestion used variables)
I have modified the code using my iPad for you - I am away from my PC for several days - and so I have not been able to test it.
Try this




Dim ArrayOfRanges As Variant
Dim ArrayOfValues As Variant
Dim i As Long

With ThisWorkbook.Sheets("Sheet1")
ArrayOfRanges = Array(.Range("A1"), .Range("B1"), .Range("F1"), .Range("G1"), .Range("K1"), .Range("M1"), .Range("P1"))
ReDim ArrayOfValues(0 To UBound(ArrayOfRanges))

For i = 0 To UBound(ArrayOfRanges)
ArrayOfValues(i) = ArrayOfRanges(i).Value
Next i

.Cells.Clear

For i = 0 To UBound(ArrayOfRanges)
ArrayOfRanges(i).Value = ArrayOfValues(i)
Next i
End With

[mokhtar]

Thanks for quick reply
perfect ! that works so well, thank you so much.

Regards

Mokhtar

[Yongle]

Glad it helped.

[p45cal]

You could try this one which won't clear the formatting of the cells you want to keep:
Sub blah()
Union(ActiveSheet.UsedRange.Offset(1), Range("C1:E1,H1:J1,L1,N1:O1"), Range(Range("Q1"), Cells(1, Columns.count))).ClearContents 'or just .clear if you want to clear formats too.
End Sub

That one will fail if you happen to have something on the very bottom row of the entire sheet.
…but which can be averted by changing the line to:
Union(ActiveSheet.UsedRange.Resize(Application.Max(1, ActiveSheet.UsedRange.Rows.count - 1)).Offset(1), Range("C1:E1,H1:J1,L1,N1:O1"), Range(Range("Q1"), Cells(1, Columns.count))).ClearContents 'or just .clear if you want to clear formats too.

[mokhtar]

thank you very much for this code mr p45cal

this mini code but works perfectly

I try to used another ranges ( like A1:A10 , D5 : E5 F1 : G10 ) but I could't

how i do this

Thanks in advance

Mokhtar

[p45cal]

I try to used another ranges ( like A1:A10 , D5 : E5 F1 : G10 ) but I could't
how i do this try:
Union(Range("B1:E4,B5:C5,B6:E10"), Rows("11:" & Application.Max(11, ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).Row)), Range(Range("H1:H10"), Cells(1, Columns.count))).ClearContents

[mokhtar]

fantastic
Mr.p45cal , Now i'm understanding the method

Words are unable to thank you for all this great help
Thank you very much Mr.Yongle
Thank you very much Mr.p45cal
Regards
Mokhtar