logo
Tags down

shadow

Delete multiple Excel Sheets in VBA


By : Yug Vohra
Date : October 18 2020, 06:10 PM
fixed the issue. Will look into that further I am using an excel Workbook for programtical generation. Once the workbook is created few of the sheets are having required data and few are blank with default templates only. , Try this:
code :
Sub DeleteEmptySheets()
    Dim i As Long, ws As Worksheet
    ' we don't want alerts about confirmation of deleting of worksheet
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        Set ws = Worksheets(i)
        ' check if cell D22 is empty
        If IsEmpty(ws.Range("D22")) Then
            Sheets(i).Delete
        End If
    Next
    ' turn alerts back on
    Application.DisplayAlerts = True
End Sub


Share : facebook icon twitter icon

VBA excel 2010 working with sheets names and delete blanc sheets


By : gear48
Date : March 29 2020, 07:55 AM
it helps some times You don't need to use .Activate. You can directly work with the sheets. Also when deleting sheets and switching off events, always use proper error handling.
Is this what you are trying?
code :
Dim ws As Worksheet

Sub Printtabs()
    On Error GoTo Whoa

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "NAME SPECIFIC 1" Then
            '~~> Process Formula
        ElseIf ws.Name = "NAME SPECIFIC 2" Then
            '~~> Process Formula
        Else
            If ws.Name <> "NTV" And WorksheetFunction.CountA(ws.Cells) = 0 Then
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        End If
    Next ws

LetsContinue:
    Application.DisplayAlerts = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Excel delete all sheets except active sheet and specified sheets using codenames


By : Martin
Date : March 29 2020, 07:55 AM
like below fixes the issue Your code is already fairly concise.
You can add the ThisWorkbook.ActiveSheet.Name to the first Case and avoid the IF.
code :
Sub delete_test()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Deletes all sheets except ActiveSheet and specified sheets using codenames
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    Select Case ws.CodeName
        Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", ThisWorkbook.ActiveSheet.CodeName
        Case Else
            ws.Delete
    End Select
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

How to delete same rows or ranges across multiple sheets in Google Sheets?


By : Ranga Babu
Date : March 29 2020, 07:55 AM
To fix the issue you can do This is a multisheet delete you can setup the object delO to delete the same range or different ranges in any of the sheets on a spreadsheet if you want them all to be the same then enter the same ranges
code :
function multiSheetDelete()
{
  var delO={sheet1:'A3',sheet2:'A1:A10',Sheet5:'A2:A20',Sheet6:'A1:A4'};
  var keys=getKeys(delO);
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var allSheets=ss.getSheets();
  for(var i=0;i<allSheets.length;i++)
  {
    var sht=allSheets[i];
    var shtnam=sht.getName();
    if(keys.indexOf(shtnam)>-1)
    {
      var rng=sht.getRange(delO[shtnam]);
      rng.clearContent();
    }
  }
    }

function getKeys(aArray)
{
  var keys = [];
  for(var key in aArray) 
  {
    if(aArray.hasOwnProperty(key)) 
    {
      keys.push(key);
    }
  }
  return keys;
}

How can I iterate through multiple excel sheets to apply simple data cleaning to all sheets?


By : user2767660
Date : March 29 2020, 07:55 AM
I hope this helps you . You can do this using lapply, a for loop, purrr::map, etc. Here is an example with lapply which should work:
code :
Mega_sheets <- lapply(Mega_sheets, function(x){
  colnames(x) <- as.character(unlist(x[6,]))
  x[c(-1,-2,-3,-4,-5,-6), ]
})

How do I delete multiple row ranges simultaneously for all excel sheets?


By : Amjad
Date : March 29 2020, 07:55 AM
With these it helps When deleting multiple rows sections start with the highest row numbers first then work down so your rows won't be affect by the shift. Something like this should solve your issue.
code :
 Sub WorksheetLoop()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("169:174").EntireRow.Delete
        ws.Range("113:118").EntireRow.Delete
        ws.Range("57:62").EntireRow.Delete
    Next ws
    End Sub
Related Posts Related Posts :
  • Not able to find unique values in excel
  • Excel stealing keyboard focus from VCL Form (in AddIn)
  • Multiplying an amount by 0.45 up until 833 then the remain by 0.25
  • Sort multiple rows Left-to-Right
  • Sorting data in columns with variable number of columns
  • Speeding up macro that goes through a workbook overwriting sheets by value
  • How can I optimize this macro that sorts and formats SQL data?
  • Copy a cell to a merged cell while keeping hyperlink value
  • Multiple IF arguments layered between cells
  • 432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
  • How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
  • Flashing animation getting interrupted by DoEvents
  • Delete text (left or right) with VBA so I only keep a specific string
  • I Bad: VBA Confusion
  • Add spaces to a string in excel vba, later used in .HTMLBody
  • Using While loop for getting the handle of rows in a particular Column
  • How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
  • FIND function in VBA - Object does not support this property or Method
  • VBA Excel: “Automation Error. Exception occurred.” when using UserForm
  • Find specific lower or uppercase
  • group the results of a matching function
  • "Standard Deviation If" in VBA
  • How to use Conditional Formatting?
  • Match Function Shows Incorrect/Repeat Position
  • Add character to the beginning of a cell that start with number
  • How can I handle DLL errors in VBA?
  • Copy a Hidden Excel Sheet
  • How to import a table from web page (with "div class") to excel?
  • format excel file after export-csv powershell
  • Cancel = True is not working in Workbook_BeforeClose
  • Is there a way to retrieve information from a text file in vba?
  • Comparing two columns and if it matches then it should be highlighted in another column
  • How to get temperature of datetime value based on the table which contains day and night wise temperature data
  • How to Define a Pivot Cache?
  • Named argument not found vba compile error
  • Calling multiple Sub's with one line
  • Excel VBA Dictionary Data Integrity Issue
  • Copying a column in excel and doubling every entry/cell. How to?
  • How to continue running a VBA macro when an SAP popup message tells us that there is no data found?
  • Define List that Changes
  • How do I search a list of characters in a text
  • Clear cell content based on another cell for each row
  • Deleting a part of a cell
  • Skip over function error and continue with next check (VLOOKUP)
  • How to fix "value used in formula is wrong data type" error when trying to convert a range into an array?
  • Reverse order of days auto-populated by macro that creates new sheet named 06/01, 06/02 and so on
  • Formula help for multiple conditions in excel conditional fomatting
  • Not in search string - It works when it is
  • Pasting values from one Excel Workbook to others
  • Excel VBA open folder and get GPS info (Exif) of each files in it (2)
  • Runtime Error 5: Removing Certain Items from VBA Collection
  • COUNTING COMMISSION
  • Why is this VBA function slow?
  • How to return worksheet or workbook name in cell
  • VBA change font properties of chart axes without using With-EndWith Statement
  • Find a string within an Excel file and get the row index
  • Referencing Public Constants in Cell Formulas
  • How to make "edit links" VBA code run faster?
  • Deleting Rows with Reverse Loop - VB
  • Clear contents of active cell if it contains specific text
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk