How to select all blank cells in a range to keep unlocked in excel.

 Select Only the Blank Cells:

• Press Ctrl + G (or F5) to open the Go To dialog box.
• Click the Special... button at the bottom left.
• Select Blanks and click OK

How not to fill filtered out cells when dragging down from another sheet

Steps:

1. Apply your filter in Sheet1.

2. Select the range where you want to paste the formula (e.g., A2:A100) but only visible cells will be selected.

   • Select the range.

   • Press F5 or Ctrl+G → Click Special… → Choose Visible cells only → OK.

3. Type your formula (e.g.):

   =INDEX(Sheet2!A:A, ROW()-1)

4. Press Ctrl+Enter (instead of just Enter).

Excel VBA Macro: Save Range (as PDF) and Send as Attachment in Email

Sub save_pdf_and_attach_in_email()

Dim OutApp As Object

Dim OutMail As Object

Dim ws As Worksheet

Dim pdf_range As Range

Dim pdf_path As String

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

Set ws = ThisWorkbook.Sheets("Sheet 1")

Set pdf_range = ws.Range("A1:C11")

pdf_path = "C:\Users\greggowaffles\Documents\Youtube Videos\Test\Stats\" & _

    "Population Data.pdf"

    

'align range in pdf

ws.PageSetup.CenterHorizontally = True

    

pdf_range.ExportAsFixedFormat Type:=xlTypePDF, _

    Filename:=pdf_path, OpenAfterPublish:=False

'create email

On Error Resume Next

    With OutMail

        .to = "team@abc.com"

        .CC = ""

        .BCC = ""

        .Subject = "Country Population Data " & Format(Date, "mm-dd-yyyy")

        .Display

        .HTMLBody = "＜BODY style = 'font-size:12pt; font-family:Calibri'＞" & _

            "Hi Team,＜p＞Please see attached pdf file.＜p＞Thanks,＜br＞Greg" & .HTMLBody

        

        .Attachments.Add pdf_path

    

    End With

    On Error GoTo 0

Set OutApp = Nothing

Set OutMail = Nothing

'Kill pdf_path

End Sub

How to get Ranking of the Students in Excel using RANK Function

=RANK(number,ref,[order])

To rank in descending order, we will use the formula =RANK(B2,($C$5:$C$10),0)

If we want unique ranks, we can use the formula:

=RANK(C5,$C$5:C$10,0)+COUNTIF($C$5:C5,C5)-1

For ascending order, the formula would be:

=RANK.EQ(C5,$C$5:C$10,1)+COUNTIF($C$5:C5,C5)-1

Prevent duplicate entry in excel.

On the Settings tab, choose "Custom" from the Allow drop down list and enter =COUNTIF($D:$D,D2)=1 into the Formula box

Contatenate text to Date to Sort

Convert 01.01.2024  to 01/01/2024 to count as date in Excel.

 =DATE(RIGHT(A2,4),MID(A2,FIND(".",A2,1)+1,FIND(".",A2,FIND(".",A2,1)+1)-FIND(".",A2,1)-1),MID(A2,1,FIND(".",A2,1)))

Extract Domain from email in Excel or Google sheet

 =TEXTAFTER(A2, "@")

or,

=MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1))

Increment the cell in Vlookup when dragging horizontally in Excel

Excel VLOOKUP with Dynamic Column Reference:

=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:B4)+1,0)

=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4)+1,0)

=VLOOKUP(A53,'Tab(4Y-R)'!A24:AU157,COLUMNS('Tab(4Y-R)'!A24:A24)+1,FALSE())

=VLOOKUP(A53,'Tab(4Y-R)'!A24:AU157,ROWS('Tab(4Y-R)'!A24:A24)+1,FALSE())

=ROWS(B$4:B4)

Export multiple records one by one from Excel

 

Sub Print1()

    For a = 1 To 10

        Sheet3.Range("L2").Value = a

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True, IgnorePrintAreas:=False

Next

End Sub

Export multiple records to PDF one by one from Excel

 Sub ExportRangeToPDF()

    For a = 3 To 10

        Sheet1.Range("D4").Value = Sheet2.Cells(a, "A")

        Sheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Naeem\OneDrive\Desktop\PrintingPDF\" & Sheet1.Range("D4").Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    Next

End Sub

Repeat Rows At Excel Bottom using VBA

 Sub repeatBotRows()

Dim botRows As Range, botCount As Long

Dim firstPgBk As Long, LasRow As Long

Dim totPages As Long, n As Long, m As Long

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Application.Calculation = xlCalculationManual

Set botRows = Range("2:7")

Sheets("Sheet1").Copy after:=Sheets("Sheets1")

ActiveSheet.Name = "printOrig"

With ActiveSheet.PageSetup

.PrintTitleRows = "$1:$1"

End With

firstPgBk = ActiveSheet.HPageBreaks(1).Location.Row - 1

botCount = botRows.Rows.Count

LasRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row

totPages = Application.Ceiling(LasRow / (firstPgBk - botCount - 1), 1)

Range(Rows(firstPgBk - botCount + 1), Rows(firstPgBk)).Select

Selection.EntireRow.Insert Shift:=xlDown

botRows.Copy Range("A" & firstPgBk - botCount + 1)

n = 2

m = 0

Do

Range(Rows(firstPgBk * n - botCount - m), Rows(firstPgBk * n - m - 1)).Select

Selection.EntireRow.Insert Shift:=xlDown

botRows.Copy Range("A" & firstPgBk * n - botCount - m)

n = n + 1

m = m + 1

Loop Until n > totPages

Application.Calculation = xlCalculationAutomatic

' ActiveSheet.PrintOut

' ActiveSheet.Delete

ActiveSheet.Buttons.Delete

Application.DisplayAlerts = True

End Sub

Repeat excel rows at bottom

 Sub MyFooter()

Dim xTxt As String

Dim xAddress As String

Dim xRg As Range

Dim xCell As Range

On Error Resume Next

xAddress = ActiveWindow.RangeSelection.Address

Set xRg  = Application.InputBox("Select the row you will insert repeatedly at the bottom:", "Kutools for Excel", xAddress, , , , , 8)

If xRg Is Nothing Then Exit Sub

For Each xCell In xRg

xTxt = xTxt & xCell.Value & " "

Next

ActiveSheet.PageSetup.LeftFooter = xTxt

End Sub

Repeating Rows at the bottom of each printed excel sheet using VBA

 Sub MyFooter()

Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range

Set Sh = Worksheets("Sheet5")

Set Rng = Sh.Range("A55:G55")

For Each c In Rng

StrFtr = StrFtr & c & " "

Next c

ActiveSheet.PageSetup.LeftFooter = StrFtr

End Sub

Hide rows in excel based on cell value using VBA

Sub HideRow()

LineStart = 2

LineEnd = 16

ColumnNumber = 2

For i = LineStart To LineEnd

If Cells(i, ColumnNumber).Value <> "YourCellValue" Then

Cells(i, ColumnNumber).EntireRow.Hidden = True

Else

Cells(i, ColumnNumber).EntireRow.Hidden = False

End If

Next i

End Sub

Generate emaill address using another column

 =IF(C2="",CONCATENATE(B2,"@","domain.com"),C2)

Generate specific range of numbers having specific digit in excel

 The following function is for generating 8 digit unique number in excel and masked with 123:

=RANDBETWEEN(12300000,12399999)

Get file path and set hyperlink funtion in Excel

 =CONCATENATE("file://",LEFT(CELL("filename",F2),FIND("[",CELL("filename",F2))-1),F2)

=HYPERLINK($G$2,F2)

Extract first word in EXCEL

=LEFT(a1,FIND(" ",a1)-1)

How To Generate Random Alpha Numeric Strings In A Range In Excel?

 =CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,10000)&CHAR(RANDBETWEEN(65,90))

Google Sheet Sigle column No Duplicate Data Validation

 Here the example is given for column A:

=COUNTIF($A$2:$A711,$A13)<2