VBA Tips Reset Used Range

How do you clear a UsedRange in VB

i.e. say when you have 50,000 rows of data & you delete, say 40,000 - how do you set the UsedRange to be just 10,000 rows, as opposed to XL still thinking it's 50,000

was trying something like
Code:

Sub rsetusedrng()

Set Sheets(4).UsedRange = Range("A1:AX" & Range("AX1").End(xlDown).Row)
  
End Sub

but no joy...

Hello.

I need to reset the range used by a macro to nothing each time i run it so i use the following code:

Code:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Cells.Delete
Selection.EntireRow.Delete
Worksheets("Sheet1").ReSetUsedRange

Something is not working because when i manually press Ctrl+Shift+End after running the above code the selected area is still the one previously used. How can i reset it to nothing?

Thanks a bunch for any helps.

Octavio

i want to create a reset button so when i press it all the values reset back to zero. this is for a board game and i don't know how to make create this button.

I am looking for VB code to solve a quite simple problem:

I have two cells with drop-down list, say C5 and C6. C6 is dependent on C5, which means when you change the value in C5, the list of options in C6 will change as well. The job I want the code to do is when I change C5, it should set C6 default value as " ", and the value in range D5:H5 as False. And when I change value in C6, C5 remains unchanged, the value in range D5:H5 should be all set as False too.

The code I manged to come up with is shown below. But when I run it, excel got an error msg saying something like stack overflow. Not sure where goes wrong. Any help will be appreciated. Thanks.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("C5") Then
Target.Offset(1, 0).Value = ""
Range("D5:H5").Value = "False"
End If

If Target = Range("C6") Then
Range("D5:H5").Value = "False"
End If

End Sub

Can someone help me write a macro that will reset a range name?

I have a range name called "Table1" that is used in a pivot summary. When I append new data to the table, I'd like to use a macro to include the appended data in the range name "Table1" so that I don't have to update it manually.

I fiddled with using the macro recorder to select the "current region" and name the range "Table1", but it appears to be selecting a defined Row and Column region, not the "current region".

Can someone assist?

Thanks!

On a worksheet the user can elect to view monthly data. The "From" and "To" months can be any they choose. For example, if they choose "JAN" and "JUN" as the "From" and "To" months, "JUL" through "DEC" are hidden from view.
I would like to be able to show "Year to Date" totals based on the "To" month. I refer to the "To" month as the "ActiveMonth".

This procedure captures the "To" month (the "ActiveMonth") based on their input:
Code:

 Option Explicit
Public Sub Find_Active_Month()

    Sheets("AnnualBudget").Select
    Sheets("AnnualBudget").Unprotect
    Sheets("AnnualBudget").Range("C4", "Z4").Select
    Range("Z4").Activate

    Do While ActiveCell.EntireColumn.Hidden = True
        If ActiveCell.EntireColumn.Hidden = True Then

            Debug.Print ActiveCell.Address
            ActiveCell.Offset(0, -1).Activate

        End If
    Loop
    ActiveCell.Offset(0, 0).Select
    ActiveMonth = Mid(ActiveCell.Address, 2, 1)
End Sub

I would like to pass the "To" month (the "ActiveMonth") to the following function:
Code:

Public ActiveMonth As String
Option Explicit
Function Sum_Variable_Cells(Cells_To_Sum As Object, strCriteria As String)
    Dim cell As Range, Total As Double

    Set Cells_To_Sum = Columns("C", ActiveMonth)
    
    For Each cell In Cells_To_Sum

        If cells(5, cell.Column) = strCriteria Then
            Total = Total + cell.Value
        End If
    Next

    Sum_Variable_Cells = Total

End Function

The Function doesen't like this line of code: Code:

Set Cells_To_Sum = Columns("C", ActiveMonth)

Of course, I've no clue what would happen if it did like the line of code.
In the worksheet cell where the function is called, the Cells_To_Sum is referred to as Cx:Zx where x is the row number. What I'm attempting to do is modify the Z (Z being December) factor to be whatever the user chooses.

I've been struggling with this for three days and cannot figure it out. Any insight you can provide will be appreciated.

Thanks...J

Hello,

I have a range. For example lets say (A1:A10). I also have an integer where based upon it, I want to reset my range.

Lets say my int is 9 what I want to do is reset the range so that it now is:

(A2:A10)

and if my int is 7, I would want it to be (A4:A10)

How can I reset my range based off of my variable?

Thanks in advance.

I have a range that I need to set to false before I run a Marco, I am doing it this way Range("myrange").Value = False. But I need to check the range to see if it is true or false before I change it and then set it back to what it was at the end of the Marco, how can this be done? Using excel '97

Basically I have a macro which copies/pastes an index/match function across some columns.

So I have a data sheet where I paste data into. Then I run the macro to run the index/match function and pull the data into a consolidated data sheet. I want to be able to delete the first set of data in the data sheet and then put new data in the data sheet, then run the macro again and have it append the new data to the end of the consolidated data sheet.

The problem that I'm having is that when I click to run the macro it starts the reference based on where the cell is. So in the macro it says "index (R4C..." so that I can copy it and paste it across and have the lookup range move with where it is being pasted. I really need it to always start at the same column.

So how can I put it in the code so that whenever I start the macro it looksup based on the same starting column in the data sheet (since I will be changing the report on a regular basis).

I am trying to reset/ClearContents within a Range of Cells.
I used Name Manager and Defined the following code & named it "ProdTally120":
Code:

='PROD TALLY'!C15:C24,'PROD TALLY'!C27:C36,'PROD TALLY'!C39:C48,'PROD TALLY'!G15:G24,'PROD TALLY'!G27:G36,'PROD TALLY'!G39:G48,'PROD TALLY'!K15:K24,'PROD TALLY'!K27:K36,'PROD TALLY'!K39:K48,'PROD TALLY'!O15:O24,'PROD TALLY'!O27:O36,'PROD TALLY'!O39:O48

I used Name Manager and Defined the following code & named it "ProdTally240":
Code:

='PROD TALLY'!C51:C60,'PROD TALLY'!C63:C72,'PROD TALLY'!C75:C84,'PROD TALLY'!G51:G60,'PROD TALLY'!G63:G72,'PROD TALLY'!G75:G84,'PROD TALLY'!K51:K60,'PROD TALLY'!K63:K72,'PROD TALLY'!K75:K84,'PROD TALLY'!O51:O60,'PROD TALLY'!O63:O72,'PROD TALLY'!O75:O84

I used Name Manager and Defined the following code & named it "ProdTally360":
Code:

='PROD TALLY'!C87:C96,'PROD TALLY'!C99:C108,'PROD TALLY'!C111:C120,'PROD TALLY'!G87:G96,'PROD TALLY'!G99:G108,'PROD TALLY'!G111:G120,'PROD TALLY'!K87:K96,'PROD TALLY'!K99:K108,'PROD TALLY'!K111:K120,'PROD TALLY'!O87:O96,'PROD TALLY'!O99:O108,'PROD TALLY'!O111:O120

I then Added an ActiveButton with the following Code:
Code:

Private Sub Reset_Click()
ActiveSheet.Unprotect ("PASSWORD")
Range("ProdTally120").ClearContents
Range("ProdTally240").ClearContents
Range("ProdTally360").ClearContents
ActiveSheet.Protect ("PASSWORD")
End Sub

When I click the Reset Button I get an error message that says:
Code:

Run-time Error '1004':
Cannot change part of a merged cell

I don't understand!
Any ideas!