Excel Solutions Archive

If you want to quickly get the data size of each worksheet in an Excel workbook, you can refer to this article. Here we will share 2 handy methods with you.

At times, when dealing with an Excel workbook which consists of a great amount of worksheets, you may want to get the data size of each worksheet. However, there is not a native feature for this requirement. So, you need to use some workarounds, such as the following Method 1. Or you can use the much smarter Method 2 to get all worksheets’ data sizes in bulk.

Method 1: Get Worksheet Data Size One by One Manually

  1. At first, open the specific Excel workbook.
  2. Then, copy the contents of an Excel worksheet.Copy Sheet Contents
  3. Next, create a new workbook and paste the copied worksheet into Sheet 1.Paste Copied Worksheet
  4. Later, save the new workbook to a Windows folder.Save New Workbook
  5. Lastly, you can check the new workbook’s data size, equal to the worksheet’s data size, in Windows Explorer, as shown in the following screenshot.Get Data Size in Windows Explorer
  6. Now, you can use this way to get the data sizes of other worksheets one by one.

Method 2: Batch Get the Data Sizes of All Worksheets via VBA

  1. To begin with, open the Excel VBA editor according to “How to Run VBA Code in Your Excel“.
  2. Then, put the following code into a project or module.
Sub GetEachWorksheetSize()
    Dim strTargetSheetName As String
    Dim strTempWorkbook As String
    Dim objTargetWorksheet As Worksheet
    Dim objWorksheet As Worksheet
    Dim objRange As Range
    Dim i As Long
    Dim nLastEmptyRow As Integer

    strTargetSheetName = "Sheet Sizes"
    strTempWorkbook = ThisWorkbook.Path & "\Temp Workbook.xls"

    With ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
         .Name = strTargetSheetName
         .Cells(1, 1) = "Sheet"
         .Cells(1, 1).Font.Size = 14
         .Cells(1, 1).Font.Bold = True
         .Cells(1, 2) = "Size"
         .Cells(1, 2).Font.Size = 14
         .Cells(1, 2).Font.Bold = True
    End With

    Set objTargetWorksheet = Application.Worksheets(strTargetSheetName)

    For Each objWorksheet In Application.ActiveWorkbook.Worksheets
        If objWorksheet.Name <> strTargetSheetName Then
           objWorksheet.Copy

           Application.ActiveWorkbook.SaveAs strTempWorkbook
           Application.ActiveWorkbook.Close SaveChanges:=False

           nLastEmptyRow = objTargetWorksheet.Range("A" & objTargetWorksheet.Rows.Count).End(xlUp).Row + 1

           With objTargetWorksheet
                .Cells(nLastEmptyRow, 1) = objWorksheet.Name
                .Cells(nLastEmptyRow, 2) = FileLen(strTempWorkbook)
           End With

           Kill strTempWorkbook
         End If
    Next
End Sub

VBA Code - Get the Data Size of Each Worksheet in an Excel Workbook

  1. After that, press “F5” to run this macro right away.
  2. Eventually, after macro finishes, back to the workbook.
  3. You’ll see a new worksheet at the very beginning, which contains the size of each worksheet, like the following image.New Sheet with Size Information

Comparison

  Advantages Disadvantages
Method 1 Easy to operate Too troublesome if there are too many worksheets in the current workbook
Method 2 Easy and convenient no matter how many worksheets exist in the workbook Perhaps the above VBA code is a bit incomprehensible for newbies

Fix Exasperating PST Issues

Users can encounter all kinds of disturbing troubles in MS Excel, ranging from unexpected shutdown or restart to severe xlsx corruption. Unquestionably, the most knotty problem is damaged Excel file in that it cannot be resolved easily. It demands you to apply an external Excel fix tool, like DataNumen Excel Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt SQL Server and outlook repair software products. For more information visit www.datanumen.com

In an Excel worksheet, if data is entered in nonadjacent cells, you may discover it a bit troublesome to select and copy the data in non-blank cells. Now, in this post, we will introduce 2 ways to quickly select all non-blank cells.

Excel users are frequently required to copy and paste data. However, sometimes, there may be some troubles. For example, there are multiple discontinuous blank cells in an Excel worksheet. In this case, it is a bit hard to select all the cells which have content. But, don’t worry. Here we will share you 2 approaches to select all non-blank cells in an Excel sheet.

Method 1: Select via “Go To Special”

  1. At the outset, open the Excel worksheet.
  2. Then, press “F5” to trigger “Go To” dialog box.Trigger "Go To"
  3. In the “Go To” dialog, click “Special” button.
  4. Next, check the “Constants” option and then “Numbers”, “Text”, “Logicals” and “Errors” options.Check Options in "Go To Special"
  5. Finally, click “OK”.
  6. When dialog box closes, as you see, all non-blank cells have been selected.Selected Non-blank Cells

Method 2: Select with Excel VBA

  1. First off, get access to Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
  2. Then, put the following code into an unused module.
Sub SelectAllNonBlankCells()
    Dim objUsedRange As Range
    Dim objRange As Range
    Dim objNonblankRange As Range

    Set objUsedRange = Application.ActiveSheet.UsedRange

    For Each objRange In objUsedRange
        If Not (objRange.Value = "") Then
           If objNonblankRange Is Nothing Then
              Set objNonblankRange = objRange
           Else
              Set objNonblankRange = Application.Union(objNonblankRange, objRange)
           End If
        End If
    Next

    If Not (objNonblankRange Is Nothing) Then
       objNonblankRange.Select
    End If
End Sub

VBA Code - Select All Non-blank Cells

  1. After that, exit the VBA editor and add this macro to Quick Access Toolbar.
  2. Now, open your desired worksheet and click the macro button.
  3. At once, all non-blank cells will be selected, as shown in the following image.Selected Cells via VBA Code

Comparison

  Advantages Disadvantages
Method 1 Easy to operate Users have to manually open “Go To” dialog box and check options every time when they need to select non-blank cells
Method 2 Easy and convenient for reuse Arise the dangers of external malicious macros

Repair Annoying Excel Troubles

Excel file is prone to errors and corruption. For instance, if MS Excel is frequently closed improperly, the file can get damaged with ease. Hence, users have to make some precautions, including backing up the files on a periodical basis. In addition, a proficient, robust and reliable xlsx fix tool, like DataNumen Excel Repair, is a matter of necessity.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including SQL Server recovery and outlook repair software products. For more information visit www.datanumen.com

At times, you may want to batch apply the format of one chart to all the others in an Excel worksheet. In this article, we will expose 2 quick methods to you.

Perhaps you have created a lot of charts in an Excel worksheet and these charts may be in different formats. For instance, some are pie charts, some are column charts and some are line charts. Now, you may want to unify the formats of all the charts. You wish to copy the format of a chart to all the other charts in the sheet. In general, most users will utilize the following Method 1 to manually do it one by one. Yet, if there are many charts to be processed, you’d better use the latter way.

Method 1: Copy Chart Format One by One

  1. First off, open the Excel worksheet that you want.
  2. Then, pitch on the model chart and right click on it.
  3. From the context menu, select “Copy” option.Copy Chart
  4. Then, select another chart.
  5. Next, click “Paste” button and select “Paste Special” in “Clipboard” group.Paste Special
  6. In the popup dialog box, select “Formats” and click “OK”.Paste Formats
  7. At once, the selected chart’s format has been modified.Target Chart's Format Is Changed
  8. Now, in this way, you can copy the source chart’s formats to the other charts one by one.

Method 2: Batch Apply with Excel VBA

  1. To begin with, launch Excel VBA editor according to “How to Run VBA Code in Your Excel“.
  2. Then, put the following code into a module.
Sub CopyChartFormatToOtherCharts()
    Dim objChart As ChartObject

    ActiveChart.ChartArea.Copy

    For Each objChart In ActiveSheet.ChartObjects
        objChart.Activate
        ActiveSheet.PasteSpecial Format:=2
    Next objChart
End Sub

VBA Code - Batch Apply the Format of One Chart to the Others

  1. After that, add this macro to ribbon or Quick Access Toolbar.
  2. Eventually, you can have a try.
  • At first, select the sample chart.
  • Then, hit the newly added macro button.Run Macro
  • Immediately, the other charts’ formats have been changed to the source one, like the following screenshot.Batch Changed Charts

Comparison

  Advantages Disadvantages
Method 1 Easy to operate Unable to process many charts in bulk
Method 2 Far faster and more effective to process multiple charts in batches Users have to keep cautious of external malicious macros.

Fix Compromised Excel Files

At times, Excel files can be corrupted or lost due to miscellaneous factors, such as human errors, like accidental deletion, or Excel crashes and so on. Therefore, it is necessary to get hold of a powerful xlsx repair utility, such as DataNumen Excel Repair. It’s earned a lot of kudos due to its effectiveness and high Excel recovery rate.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf fix and outlook repair software products. For more information visit www.datanumen.com