vba Clear all except range

[KDS14589]

Dim ws As Worksheet

Set ws = ShGE03

With ws

Dim RowDataStart As Long

RowDataStart = 5

Dim WSRowEnd As Long

WSRowEnd = .Cells(Rows.Count, 5).End(xlDown).Row

Dim RowDataEnd As Long

RowDataEnd = .Cells(Rows.Count, 5).End(xlUp).

Dim rowdata As Long

'For rowdata = RowDataStart To RowDataEnd 'for referance

End With

Above code is for one of my variables.
I wish to clear data and formatting from Column (“A”) below Row 49 and

except data and formatting in .Range(.Cells(5, "E"), .Cells(rowdata, "I")) and

except data and formatting in .Range(.Cells(5, "Q"), .Cells(rowdata, "Q")) and

clear Column (S) below Row 21 and

clear Column (T) below Row 21 and

clear Column (V) below Row 6

All remaining Columns and Rows should be cleared.



''''''''''''''''tried but will not work

Dim name3 As String

Dim red3 As long

Dim green3 As long

Dim blue3 As long

Dim index3 As long

Dim item3 As String

Dim Row3 as String

Dim add3 As String

Dim brightness3 As long



'With ws

For Rowdata = RowDataStart To RowDataEnd

name3 = .Range("E5:E" & rowdata).Value

red3 = .Range("F5:F" & rowdata).Value

green3 = .Range("G5:G" & rowdata).Value

blue3 = .Range("H5:H" & rowdata).Value

index3 = .Range("I5:I" & rowdata).Value

item3 = .Range("Q5:Q" & rowdata).Value

Row3 = .Range("S5:S15").Value

add3 = .Range("T5:T15").Value

brightness3 = .Range("V5").Value

Next

End With



With ws

.Cells.Clear

End With



With ws

For Rowdata = RowDataStart To RowDataEnd

.Range("E5:E" & rowdata).Value = name3

.Range("F5:F" & rowdata).Value = red3

.Range("G5:G" & rowdata).Value = green3

.Range("H5:H" & rowdata).Value = blue3

.Range("I5:I" & rowdata).Value = index3

.Range("Q5:Q" & rowdata).Value = item3

.Range("S5:S15").Value = Row3

.Range("T5:T15").Value = add3

.Range("V5").Value = brightness3

Next

End With

 

[PeteWright]

@KDS14589 I'm a bit too lazy to analyze your VBA code and see what you want to keep.

Could you post which cells to keep? Knowing that, it will be easy to find the cells to be cleared (it's just the inverse "matrix").
And please explain your variables a little bit.
e.g.: Is ShGE03 a variable (holding the name of a worksheet) or just the name of the worksheet?

 

[KDS14589]

@KDS14589 I'm a bit too lazy to analyze your VBA code and see what you want to keep.

Could you post which cells to keep? Knowing that, it will be easy to find the cells to be cleared (it's just the inverse "matrix").
And please explain your variables a little bit.
e.g.: Is ShGE03 a variable (holding the name of a worksheet) or just the name of the worksheet?

Sorry for my post.
I was suffering from a server head cold while trying to solve this, so I overlooked a simple mistake. Now that I'm better I changed my array Dims to all Variants and now the code works.
Hope I didn't bother you too much.

Dim name3 As Variant
Dim red3 As Variant
Dim green3 As Variant
Dim blue3 As Variant
Dim index3 As Variant
Dim item3 As Variant
Dim Row3 As Variant
Dim add3 As Variant
Dim brightness3 As Variant


With ws
For rowdata = RowDataStart To RowDataEnd
name3 = .Range("E5:E" & rowdata).Value
red3 = .Range("F5:F" & rowdata).Value
green3 = .Range("G5:G" & rowdata).Value
blue3 = .Range("H5:H" & rowdata).Value
index3 = .Range("I5:I" & rowdata).Value
item3 = .Range("Q5:Q" & rowdata).Value
Row3 = .Range("S5:S15").Value
add3 = .Range("T5:T15").Value
brightness3 = .Range("V5").Value
Next
End With


With ws
.Cells.Clear
End With


With ws
For rowdata = RowDataStart To RowDataEnd
.Range("E5:E" & rowdata).Value = name3
.Range("F5:F" & rowdata).Value = red3
.Range("G5:G" & rowdata).Value = green3
.Range("H5:H" & rowdata).Value = blue3
.Range("I5:I" & rowdata).Value = index3
.Range("Q5:Q" & rowdata).Value = item3
.Range("S5:S15").Value = Row3
.Range("T5:T15").Value = add3
.Range("V5").Value = brightness3
Next
End With