# Create empty range or clear range contents without using .ClearContents

#### Blubber

##### New Member
Hey everyone,

I have one large range that I need to split into two smaller ranges, so I was creating two new ranges, setting them equal to a row in the original range, then using the .ClearContents method to empty the new ranges, so I could repopulate them with the data I want.

I slowly commented out code, starting from the bottom up and discovered that
the .ClearContents method, for some reason, would overwrite the first row in the original range with a blank row.

I tried to copy the first row before the code executed the .ClearContents and then add it to the top of the resulting range, but that failed no matter what I tried.

Does anyone know how to empty a range of it's contents without using the .ClearContents method?

Code:
``````If changedate <> Empty Then

Dim MD6800range As range, PS118range As range
Dim MD6800rownum As Long, PS118rownum As Long

Set MD6800range = totalRange(1).EntireRow
Set PS118range = totalRange(1).EntireRow
[COLOR="SeaGreen"]  MD6800range.ClearContents
PS118range.ClearContents
[/COLOR]
MD6800rownum = 0
PS118rownum = 0

For I = 1 To rownum
If totalRange(I, 3) < changedate Then
Set MD6800range = Union(MD6800range, totalRange(I, 3).EntireRow)
MD6800rownum = MD6800rownum + 1
End If
If totalRange(I, 3) >= changedate Then
Set PS118range = Union(PS118range, totalRange(I, 3).EntireRow)
PS118rownum = PS118rownum + 1
End If
Next I``````

Thanks,
Blubber

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
You can try checking if those ranges are empty or not FIRST then execute .ClearContents.
or even loop through each cell in that range and set it to "" by using For Each c in MD6800range where c is dimmed as Range.

You can try checking if those ranges are empty or not FIRST then execute .ClearContents.
or even loop through each cell in that range and set it to "" by using For Each c in MD6800range where c is dimmed as Range.

I set the new ranges equal to the first row in the old range, so they shouldn't be empty. I just tried looping through and setting every cell in the new ranges = "", but the first row still go overwritten with a blank row.

Here's what happens:

Your code does not show what totalRange is.
So, we can not tell whether the range for totalRange is correct.

The code you have sets MD6800range as the first row of totalRange
so, when you try to clear contents in MD6800range, it will only get rid of the first row of totalRange.

Code:
``Set MD6800range = totalRange(1).EntireRow``

The code for totalRange should be correct, it works for all the other instances when there is not a date change, but when there is a date change I need to split totalRange into two separate ranges- one to contain information from before the date change and one to contain info from after the date change.

Here's the code I used for totalRange and the other ranges that feed into it.

Code:
``````Set myRange = Cells.Find(What:=FRTextBox, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False)

Set totalRange = myRange
'
'        Set saveRow = Cells.Find(What:=FRTextBox, LookIn:=xlValues, _
'           LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
'           , MatchCase:=False)
'        Set saveRow = saveRow.EntireRow

Dim TheLastRow As Long

TheLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For I = 1 To TheLastRow
If Cells(I, 1).Value = FRTextBox Then
Set totalRange = Union(totalRange, Cells(I, 1).EntireRow)
rownum = rownum + 1
End If
Next I``````

Here's the last bit of code that affects totalRange:

Code:
``````myRange.EntireRow.Insert

Set myRange = Union(myRange, myRange.Offset(-1, 0))

Set totalRange = Union(totalRange.EntireRow, myRange.EntireRow)

totalRange.Select

totalRange.Sort Key1:=totalRange.Columns(3) _
, Order1:=xlAscending``````

myRange in the first code section finds the FR(column 1 data) the user inputs, then adds a new line for data that the user inputs. totalRange is set equal to myRange and then data is looped through to find other rows with the same FR, then totalRange is sorted according to the date. Sorry if the code is ugly, I have about a 10 days worth of VBA experience.

Thanks for the help so far kpark91

Edit: saveRow is something I tried out to see if I could set a range equal to the first value in that has the same FR the user inputs. I was hoping to add it to the top of the range at the end, but that failed, so I commented it out.

Last edited:
Nevermind, I sorted it by adding:

Code:
``Set MD6800range = Union(saveRow, MD6800range)``

outside of the massive if statement I had for if the date is changed.

IT LIVES!

Replies
4
Views
237
Replies
3
Views
49
Replies
1
Views
74
Replies
7
Views
386
Replies
5
Views
237

Threads
1,203,625
Messages
6,056,398
Members
444,862
Latest member
more_resource23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back