en
Michael Alexander

101 Ready-To-Use Excel Macros

Beri tahu saya ketika buku ditambahkan
Untuk membaca buku ini unggah file EPUB atau FB2 ke Bookmate. Bagaimana cara mengunggah buku?
Save time and be more productive with this helpful guide to Excel macros! While most books about Excel macros offer only minor examples, usually aimed at illustrating a particular topic, this invaluable resource provides you with the tools needed to efficiently and effectively program Excel macros immediately. Step-by-step instructions show you how to create VBA macros and explain how to customize your applications to look and work exactly as you want them to. By the end of the book, you will understand how each featured macro works, be able to reuse the macros included in the book and online, and modify the macro for personal use.
Shows you how to solve common problems with the featured macros, even if you lack extensive programming knowledge Outlines a problem that needs to be solved and provides the actual Excel macro, as well as the downloadable code, to solve the problem Provides an explanation of how each macro works and where to use the macro With 101 Ready-to-Use Excel Macros, Microsoft MVP Michael Alexander helps you save time, automate tasks, and ultimately be more productive.
Buku ini saat ini tidak tersedia
320 halaman cetak
Sudahkah Anda membacanya? Bagaimanakah menurut Anda?
👍👎

Kutipan

  • pablo emilio torres silvamembuat kutipan7 tahun yang lalu
    next cell. After all cells in the target range are activated, the macro ends.
    How to use it
    To implement this macro, you can copy and paste it into a standard module:
    1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
    2. Right-click the project/workbook name in the Project window.
    3. Choose Insert⇒Module.
    4. Type or paste the code.
    Macro 57: Hide All Rows but Those Containing Duplicate Data
    With the previous macro, you can quickly find and highlight duplicates in your data. This in itself can be quite useful. But if you have many records in your range, you may want to take the extra step of hiding all the non-duplicate rows. Doing so exposes the duplicate values further because they will be the only rows showing.
    How it works
    This macro enumerates through the cells in the target range, leveraging the For Each statement to activate each cell one at a time. We then use the CountIf function to count the number of times the value in the active cell occurs in the range selected. If that number is one, we hide the row in which the active cell resides. If that number is greater than one, we format the cell yellow and leave the row visible.
    Sub Macro57()
    ‘Step 1: Declare your
  • pablo emilio torres silvamembuat kutipan7 tahun yang lalu
    1. Step 1 declares two Range object variables, one called MyRange to hold the entire target range, and the other called MyCell to hold each cell in the range as the macro enumerates through them one by one.
    2. Step 2 fills the MyRange variable with the target range. In this example, we are using the selected range — the range that was selected on the spreadsheet. You can easily set the MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a named range, you can simply enter its name: Range(“MyNamedRange”).
    3. Step 3 starts looping through each cell in the target range, activating each cell.
    4. The WorksheetFunction object provides a way for us to be able to run many of Excel's spreadsheet functions in VBA. Step 4 uses the WorksheetFunction object to run a CountIf function in VBA.
    In this case, we are counting the number of times the active cell value (MyCell.Value) is found in the given range (MyRange). If the CountIf expression evaluates to greater than 1, the macro changes the interior color of the cell.
    5. Step 5 loops back to get the
  • pablo emilio torres silvamembuat kutipan7 tahun yang lalu
    Sub Macro56()
    ‘Step 1: Declare your variables
    Dim MyRange As Range
    Dim MyCell As Range
    ‘Step 2: Define the target Range.
    Set MyRange = Selection
    ‘Step 3: Start looping through the range.
    For Each MyCell In MyRange
    ‘Step 4: Ensure the cell has Text formatting.
    If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then
    MyCell.Interior.ColorIndex = 36
    End If
    ‘Step 5: Get the next cell in the range
    Next MyCell
    End Sub

Di rak buku

fb2epub
Seret dan letakkan file Anda (maksimal 5 sekaligus)