Excel VBA Macro to Close a Workbook without Saving and Reopen

Excel VBA Macro to Close a Workbook without Saving and Reopen

I wrote this macro and use it all the time when writing new macros. Feel free to use.

 

Sub ClosewithoutsaveAndReopen()
‘Defines Variables
Dim nameOfCurrentWorkbook As Variant
Dim nameOfCurrentWorkbookPath As Variant

‘Finds the name of the current workbook
nameOfCurrentWorkbook = ActiveWorkbook.Name

‘finds the current path of hte workbook
nameOfCurrentWorkbookPath = ActiveWorkbook.Path

ActiveWorkbook.Close False
‘Reopens the same worbook you just closed
‘If in the same workbook
Workbooks.Open (nameOfCurrentWorkbookPath & “\” & nameOfCurrentWorkbook)
‘if in a specific workbook
‘Workbooks.Open (“C:\Users\VMWindows7\Desktop\SAS CARB 2012_44.xlsx”)
End Sub

 

 

Excel VBA Macro to Close a Workbook without Saving and Reopen

How to make a Macro Run when you open a Word Document- WORD 2007-2013 [SOLVED]

How to make a Macro Run when you open a Word Document- WORD 2007-2013

 

How to make a Macro Run when you open a Word Document- WORD 2007-2013

Name the Macro “AutoOpen”

Example:

Sub AutoOpen()
‘Insert VBA Code to run automatically here
End Sub

Auto Run a Macro in Word

Auto Run a Macro in Word

 

VBA to Calculate only a Range or Worksheet (Excel 207/2010 VBA Macro))

The first step is to turn off auto calculations.

Application.Calculation = xlCalculationManual

This code only calculates a certain Range

Range("A1").Calculate

This code only calculates a certain Worksheet Tab

Sheets(1).Calculate

You may want to do this with large workbooks or slow computers to greatly increase speed.

 

VBA Macro to Delete Columns with Specific Values (Excel 2010 CODE)

Sub delete_columns_with_values()

Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
‘We use the ActiveSheet but you can also use Sheets(“MySheet”)
Set sh = ActiveSheet

‘We search in row 1
Set myRng = sh.Range(“1:1”)

‘Add more search strings if you need
myStrings = Array(“search string 1”, “search string 2”)

With sh

‘We select the sheet so we can change the window view
.Select

‘If you are in Page Break Preview Or Page Layout view go
‘back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

‘Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

‘We will search the values in MyRng in this example
With myRng

For I = LBound(myStrings) To UBound(myStrings)
Do
Set FoundCell = myRng.Find(What:=myStrings(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
‘Use xlPart If you want to search in a part of the FoundCell
‘If you use LookIn:=xlValues it will also delete rows with a
‘formula that evaluates to “Ron”
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireColumn.Delete
End If
Loop
Next I

End With

End With
End Sub

VBA Macro to Delete all Rows that contain a value (Excel 2010 VBA Training)

VBA Macro to Delete all Rows that contain a value  (Excel 2010 VBA Training)

Notes:

You can replace this part of the macro

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

With this if you want to fill in the Firstrow and the Lastrow yourself.

Firstrow = 4
Lastrow = 100

Or with this if you want to fill in the Firstrow and let the code find the Lastrow in the column.

Firstrow = 1
Lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row

The code below will delete every row in the usedrange with “ron” in the A column.
If .Value = “ron” Then .EntireRow.Delete

or

Delete all rows that DO NOT have the word ron

If .Value <> “ron” Then .EntireRow.Delete

I use the A column in my example, change the A to your column in this code line.
With .Cells(Lrow, “A”)

Change ron and the A to your search word and column before you try the macro.
See the examples below the macro if you want to do something different

More examples here:

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub