User Posts: Shamima
2
Excel Percentile Rank Inc vs Exc
0

Based on the dataset various times we need to rank or sort them from least to greatest. In Excel, we can use it using the PERCENTILE function. But for better ...

2
How to Format Phone Number with Extension in Excel (3 Easy Ways)
0

You may need to format your phone numbers with an extension you can easily do it by using Excel in-built features and formulas. In this article, we will learn ...

1
If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)
0

Whenever you want to search for any specific text to do some operations you can easily do it in Excel. In this article, I’m going to show you in Excel that if ...

2
How to Shift Cells Down in Excel without Changing Formula (4 Methods)
0

Normally Formulas are associated with the referenced cell but for any reason or circumstances if you want or wish to shift cells down in Excel without changing ...

2
Excel Formula to Calculate Average Annual Compound Growth Rate
0

In financial data, it is very much important to know the annual growth rate. Average annual compound and annual growth rate help to distinguish where the ...

1
Hyperlink in Excel Not Working (3 Reasons & Solutions)
0

In Excel sheets, we often use Hyperlinks to link any sheet or page. Sometimes Hyperlinks may give you reference errors or links can be broken etc. In this ...

2
How to Sort Numbers in Excel (8 Quick Ways)
0

To do analysis or to keep numbers in order, Sorting is important. In Excel, there are a couple of ways to Sort numbers. There are two possible ways to Sort ...

0
How to Remove Read Only from Excel (7 Easiest Ways)
0

An Excel file can be in Read Only mode for two reasons one is if any author has purposely done it for security issues otherwise anyone downloaded the file from ...

0
How to Remove Percentage in Excel (5 Quick Ways)
0

In an Excel sheet, you can use different kinds of formats based on your need. Percentage is one of the Number Format which is used to show a relative value ...

0
How to Remove Filter in Excel (5 Easy & Quick Ways)
0

In the Excel sheet, we do apply Filter to do an analysis of particular data. While showing the required data Filter hides other data. Anyone may need the other ...

0
How to Use VBA Mod Operator (9 Examples)
0

The VBA Mod is not a function whereas MOD is a function in an Excel worksheet. VBA Mod is an operator which divides two numbers and returns the remainder. The ...

0
How to Trim Spaces in Excel (8 Easiest Ways)
0

In Excel, while importing data from different sources or creating a dataset there stays a possibility of having extra unwanted spaces. Sometimes extra spaces ...

0
How to Use Calculated Field in Excel Pivot Table (8 Ways)
0

In Excel you can create a Pivot Table from any dataset, Pivot Table is useful when you need a new data point that can be obtained by using existing data points ...

0
How to Split First And Last Name in Excel (6 Easy Ways)
0

In Excel, you can split your full name into separate columns. There are a couple of ways to split first name and last name. You can use Excel inbuilt features ...

0
How to Use Less Than Or Equal to Operator in Excel (8 Examples)
0

Microsoft Excel has 6 logical operators which are also known as comparison operators. And Less Than or Equal to (<=) is one of them. The Less Than or Equal ...

Browsing All Comments By: Shamima
  1. Hi Exceler,

    Here, I tried the first method for 50 sets of data and it worked but you have to change the cell references based on your dataset. For your better understanding, I am attaching the images along with the formula.
    =INDEX($D$5:$D$30,MATCH(1,MMULT(--($B$5:$C$30=F5),TRANSPOSE(COLUMN($B$5:$C$30)^0)),0))

    The images of datasets

    Image of dataset

    Here, I used the formula for the entire dataset. I changed the references based on my dataset.

    Formula

    Output for 50 values:

    Output

    Note: If your dataset is very large kindly send us your dataset

    Thanks
    Shamima Sultana

  2. Hi Tom Lynham

    Hope you are doing well. Here, I updated the article which implies the title and the explanation.

    By following above explained methods it is possible to print colorful gridlines but you must use a color printer.

    If this update doesn’t help you or you have further queries kindly let us know.

    Thanks
    Shamima Sultana

  3. Hello Mark,

    We uploaded the Excel files again, you can check these files also. If you find any difficulty opening the file let us know.

    Thanks

  4. Hello Mark,
    Hope you are doing well.
    Whenever I try to download this workbook it works fine without a password. To be reassured a couple of my teammates also downloaded this file they also didn’t face any difficulty.
    As I haven’t used any password for this worksheet. I really want to know what caused such issues while you downloaded the file.
    Here, I will show you what it looks like when I download the file again.

    After downloading the file Excel shows a warning message. You have to click on Enable Editing.

    Excel sheet name in formula dynamic

    Later, the downloaded file will be available to use or you can make any changes you want.

    Solution

    N.B. If this solution doesn’t work for you. Kindly sent me the screenshots of the problem.

    Thank you.

    Regards
    Shamima Sultana

  5. Hi Froggy,
    Hope you are doing well. Thanks for reaching out to us with your issue.

    As you intend to apply the above code to a workbook with multiple sheets so you need to add some extra lines in your code to define all available worksheets or selected worksheets.

    Here, I will give you two solution
    1. For selected sheets
    2. For all the available sheets in a workbook

    1. If you want to apply the same code in some selected sheets then use the code given below where I declared sheets names by using an Array.

    Option Explicit
    Sub AddSpaceBetweenRows()
    Dim all_sheets As Worksheet
    Dim rng As Range
    Dim i As Long
    Dim all As Variant

    Set all = sheets(Array(“VBA1”, “VBA2”))

    For Each all_sheets In all
    all_sheets.Select
    Set rng = Range(“B5:E9”)
    For i = rng.Rows.Count To 2 Step -1
    rng.Rows(i).EntireRow.Insert
    Next i
    Next
    End Sub

    2. To loop through all the available sheets of your workbook you will need to use the code given below where I declared all_sheets and used an extra For Loop.

    For your better understanding, I’m providing the modified code here,

    Option Explicit
    Sub AddSpaceBetweenRows()
    Dim all_sheets As Worksheet
    Dim rng As Range
    Dim i As Long

    For Each all_sheets In Worksheets
    all_sheets.Select
    Set rng = Range(“B5:E9”)
    For i = rng.Rows.Count To 2 Step -1
    rng.Rows(i).EntireRow.Insert
    Next i
    Next
    End Sub

    Note: Based on your Excel file you will need to change the sheet name and the cell range selection.

    For further queries comment down below.

  6. Hello Naveed,

    Hope you are doing well. Kindly send me the details of your problem including the Excel file via my Gmail account [email protected]

    Thank you

  7. Hi Helen,
    I’m glad that our article helped you. For any types of Excel related problems kindly check out our ExcelDemy site.

    Thanks
    Shamima

  8. Hello Michelle,
    Hope you are doing well. If you need to run the macro after the first use it will create new sheets with the updates you made on your dataset.

    It won’t automatically update the workbooks created previously (Book1, Book2, and Book3) rather it will create Book4, Book5, and Book6 with the updated dataset.

    Thanks
    Shamima Sultana

  9. Hi A,

    Thanks for your appreciation.

  10. Hi Sikander,
    Hope you are doing well.
    To get the DateValue for February, click on the drop-down option of Month and then select February.

    monthly staff attendance

    Then you will get the DateValue and the rest will be updated automatically.

    2. Monthly Stuff Attendance

    Note: If you want to type the month name in that cell you have to be careful with the spelling of the month name.

    Thanks

    Regards
    Shamima Sultana

  11. Hi Deon Bailey,
    Hope you are doing well. Thanks for reaching out to me with your issue.

    As you didn’t share your Excel file that’s why it is hard to understand your sheet name and where is the problem occurring.

    But you need not to worry, I’m giving you a possible solution so that you can add data to your selected sheet to the last row.

    The reason for overwriting the added data is your code wasn’t finding the last row it was showing 1 as the last row number.

    Here, I added data according to my dataset I used in this article. I commented out your code and added some required lines.

    Sub Insert_Value_from_LastRow()

    ‘TargetSheet = Cmb_Months.Value
    ‘If TargetSheet = “” Then
    ‘Exit Sub
    ‘End If
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets(“Dataset”)
    targetSheet.Activate
    ‘lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    MsgBox LastRow

    ‘ActiveSheet.Cells(LastRow + 6, 2).Value = Cmb_Area.Value
    ‘ActiveSheet.Cells(LastRow + 6, 3).Value = Txt_Ln_Manager.Value
    ‘ActiveSheet.Cells(LastRow + 6, 4).Value = Txt_FName.Value
    ‘ActiveSheet.Cells(LastRow + 6, 5).Value = Txt_Surname.Value
    ‘ActiveSheet.Cells(LastRow + 6, 6).Value = Txt_S_Number.Value

    ActiveSheet.Cells(LastRow + 3, 2).Value = “Rachel Ross”
    ActiveSheet.Cells(LastRow + 3, 3).Value = “Germany”
    ActiveSheet.Cells(LastRow + 3, 4).Value = “laptop”
    ActiveSheet.Cells(LastRow + 3, 5).Value = 4567

    End Sub

    Note: Whenever you want to insert or add data after a particular row it is better to see the last row number by using MsgBox. It will help you to understand why data is overlapping.

    I also added the images.

    reply of comment

    reply of comment

    For further queries, you can send me your Excel file.

    Thanks
    Shamima Sultana

ExcelDemy
Logo