Create empty range or clear range contents without using .ClearContents

[Blubber]

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?

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

 

[kpark91]

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.

 

[Blubber]

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:

 

[kpark91]

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.

Set MD6800range = totalRange(1).EntireRow

 

[Blubber]

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.

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:

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.

 

[Blubber]

Nevermind, I sorted it by adding:

Set MD6800range = Union(saveRow, MD6800range)

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

IT LIVES!