Wednesday, November 13, 2013

VBA Code to combine sheets into one

Option Explicit

Sub CopyFromWorksheets()
Dim wrk As Workbook  'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range     'Range object
Dim colCount As Integer 'Column count in tables in the worksheets
    
    Set wrk = ActiveWorkbook 'Working in active workbook
    
    For Each sht In wrk.Worksheets
        If sht.Name = "Master" Then
            MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
                    "Please remove or rename this worksheet since 'Master' would be" & _
                    "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
            Exit Sub
        End If
    Next sht
    
    'We don't want screen updating
    Application.ScreenUpdating = False
    
    'Add new worksheet as the last worksheet
    Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
    'Rename the new worksheet
    trg.Name = "Master"
    'Get column headers from the first worksheet
        'Column count first
        Set sht = wrk.Worksheets(1)
        colCount = sht.Cells(1, 255).End(xlToLeft).Column
        'Now retrieve headers, no copy&paste needed
        With trg.Cells(1, 1).Resize(1, colCount)
            .Value = sht.Cells(1, 1).Resize(1, colCount).Value
            'Set font as bold
            .Font.Bold = True
        End With
    
    'We can start loop
    For Each sht In wrk.Worksheets
        'If worksheet in loop is the last one, stop execution (it is Master worksheet)
        If sht.Index = wrk.Worksheets.Count Then
            Exit For
        End If
        'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
        Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
        'Put data into the Master worksheet
        trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
    Next sht
    'Fit the columns in Master worksheet
    trg.Columns.AutoFit
    
    'Screen updating should be activated
    Application.ScreenUpdating = True
End Sub

Tuesday, November 12, 2013

Map & Unmap drives via VBA

VBA Code to map and unmap a drive in the system:

Sub Map_Drive()
Dim oNetwork As Object, sDrive As String, sPath As String

Set oNetwork = CreateObject("WScript.Network")
sDrive = "A:"
sPath = "\\Server1\ABC"
oNetwork.MapNetworkDrive sDrive, sPath

sDrive = "B:"
sPath = "\\Server2\XYZ"
oNetwork.MapNetworkDrive sDrive, sPath

End Sub
----------------------------------------------------------
Sub Unmap_Drive()
Dim objNetwork As Object

Set objNetwork = CreateObject("WScript.Network")

objNetwork.RemoveNetworkDrive "A:" ', bForce:=True
objNetwork.RemoveNetworkDrive "B:" ', bForce:=True
End Sub

Wednesday, November 6, 2013

Working with Name Manager in Excel

Name Manager makes it easy to work in Excel. With the help of Name Manager you can define a range of cells by a name and store it. You can refer to the name instead of mentioning the range every time. Here is a simple VBA code to create names.

Lets say we have the following data. Column A has the Names and column B has the formula (image below)



Use the following VBA code to create the names. If the name is already available, it will overtire.

Sub CreateDefineName()
Application.DisplayAlerts = False
Sheets("Sheet1").Activate
Row = 1
Do While Cells(Row, 1) <> ""
DName = Cells(Row, 1)
Frm = Cells(Row, 2)
ActiveWorkbook.Names.Add Name:=DName, RefersTo:=Frm
Row = Row + 1
Sheets("Sheet1").Activate
Loop
Application.DisplayAlerts = True
End Sub

Tuesday, November 5, 2013

VBA code to store value in a cell

VBA code to store user defined value in user defined cell(s).

Sub Test()
Dim myrange As Range
Dim mycell As Integer
Dim i As Range
On Error Resume Next

Set myrange = Application.InputBox(prompt:="enter a range of cell", Title:="Specify range", Type:=8)

For Each i In myrange

mycell = Application.InputBox(prompt:="Enter a number", Type:=1)

i = mycell

Next i
End Sub

Thursday, October 31, 2013

Outlook mail draft & coverting data into html format

Below is the code to create draft emails and paste range of data from Excel into the body of the email.

'Mail Draft coding
Private Function Mail_Range_Outlook_Body(DRng As String)
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

'Application.EnableEvents = False
Set rng = Nothing
Set rng = SW_Rpts.Range(DRng)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
    .To = SW_Rpts.Range("AG1").Value
    .CC = SW_Rpts.Range("AG2").Value
    .BCC = ""
    .Subject = SW_Rpts.Range("AG3").Value
    If SW_Rpts.Range("AG4").Value <> "" Then .Attachments.Add SW_Rpts.Range("AG4").Value
    .HTMLBody = RangetoHTML(rng)
    .Display   '.Send
    .Save
    .Close olPromtForSave
End With
'Application.EnableEvents = True

Set OutMail = Nothing
Set OutApp = Nothing
End Function

'Coverting data into html format
Public Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "\Surveys_Weekly_Temp.htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).Range("A1:J" & (Range("A100").End(xlUp).Row + 3)).Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

TempWB.Close SaveChanges:=False
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Saturday, October 26, 2013

Microsoft Excel

Microsoft Excel is developed by Microsoft and Excel is a part of MS-Office package. We all use Excel in our day to day work. It is used for various purposes like storing employee details, graphs, pivots to visually present the data, etc.

For example, below image contains sales data by store, region and category. It is also presented graphically. Graphical presentation of data in Excel makes it more easy to understand by business.

If you click on fx button you can see the list of functions Excel has. You can also create your own function using VBA macro.

How To Shutdown Your Computer Remotely Via Email For Microsoft OS

You might have come across with a situation when you have kept your system to perform some tasks while you are travelling. At the same time you want to make sure the system shuts down after the job is performed. You can do this by triggering an event from Outlook.

Refer to the below link to understand how you can shutdown your computer remotely via email:

Thursday, October 17, 2013

List out named ranges with VBA

You may have a list of named ranges in Excel and you want to list down all in a sheet.
Use the below code to get the list of all the available named ranges in the workbook.

Sub ListNames()
Dim wks As Worksheet
Set wks = Worksheets.Add
wks.Range("A1").ListNames
End Sub

Tuesday, October 15, 2013

Keyboard shortcuts

Some useful keyboard shortcuts:

Shift+Ctrl+1, 2, 3, 4 or 5" = Numeric, Time, Date, $ and %.

Run Macro if cell value changes

There are certain situations when one wants to execute the code based on a particular cell. If the value of that particular cell changes the code should execute,here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$BA$27" Then
        'Do your stuff here
    End If
End Sub

Tuesday, September 10, 2013

Excel & Outlook

Good link to learn how Excel macro can be used along with Outlook:

Sunday, September 8, 2013

Progress bar in excel

Refer to the following link for including progress bar in your VBA code:

Saturday, September 7, 2013

Get the weekday in Excel

Use the below formula to get the weekday.

=TEXT(WEEKDAY(NOW()),"dddd")

Thursday, September 5, 2013

Code to go to the top cell

VBA Code to go to the top cell in a sheet:

Private Sub Workbook_Open()

With ActiveWindow

.ScrollRow = 1

.ScrollColumn = 1

Call Cells(.ScrollRow, .ScrollColumn).Select

End With
End Sub

Tuesday, September 3, 2013

Get to last row in a column

How to get to the last row in a column using VBA:

Dim LastRow As Long
LastRow = Range("I" & Rows.Count).End(xlUp).Row

'I is the column and LastRow is a variable

Delete rows using Loop

VBA code to loop thru rows within column and delete rows containing a certain value:

Dim LastRow As Long 
Dim I As Long 
 
LastRow = Range("D" & Rows.Count).End(xlUp).Row 
 
For I = LastRow To 2 Step -1  'Change the row number
    Set rng = Range("D" & I) 
    If rng.Value= "ABC" Then   'Change the condition
        rng.EntireRow.Delete 
    End If 
     
Next I 

Monday, September 2, 2013

Negative closest value to zero:

Get the negative closest value to zero:


Saturday, August 31, 2013

Get URL from an hyperlink in Excel

VBA code to get the URL address from a link:

'Extract URL from hyperlink
Function HLink(rng As Range) As String
  If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

Monday, June 3, 2013

Send emails through Outlook using Excel

Here is the VBA code to send emails from Outlook:

Sub SendEmails()
Dim OlApp As Object
Dim OlMail As Object

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(olMailItem)

With OlMail
    .Display
    .To = "aaa@gmail.com"
    .CC = "bbb@gmail.com"
    .Subject = "Test Email"
    .Attachments.Add "C:\Test.xlsx"
    .Body = "Test email"
    .Save
    .Close olPromtForSave
    .Send
End With

End Sub

Tuesday, May 14, 2013

Pasting data in the reverse order

You might have come across with situation where you wanted to paste your data in reverse order. In the example below we want the numbers in Data column in reverse order. This can be done using excel formula.

Data Reverse Data
1 5
2 4
3 3
4 2
5 1


Following formula can be used:

=INDEX($A$2:$A$6,ROWS(A2:A$6))

Saturday, May 11, 2013

Copy data from Excel to Notepad

VBA Code to copy data from Excel to Notepad:


Sub copyPastedata_in_Notepad()
With Application
Selection.Copy
Shell "notepad.exe", 3
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With

End Sub

Tuesday, May 7, 2013

VBA Code to Collapse and Expand columns

VBA Code to Collapse and Expand columns based on the given criteria:


Sub Collapse_Expand()
Dim w As Worksheet
Dim pt As PivotTable
Dim FinYear As String
Dim QTD As String

FinYear = "FY" & Right(Date, 2)
QTD = "Q3                             "
        
        For Each w In ThisWorkbook.Worksheets
            If ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2" Then
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").ShowDetail = False
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").ShowDetail = False
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = False
                
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").PivotItems("Rev").ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").PivotItems("Cost").ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").PivotItems(FinYear).ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").PivotItems(QTD).ShowDetail = True
            End If
                On Error Resume Next
                Sheets(ActiveSheet.Index + 1).Activate
                If Err.Number <> 0 Then Sheets(1).Activate
        Next
End Sub

Saturday, April 27, 2013

Zip Files using VBA

VBA Code to zip files:

Private Sub ZipFile_FX(ZipFileName As String, fileToBeZipped As String)
Const ZIPEXELOCATION = "c:\program files\winzip\winzip32.exe"
Shell ZIPEXELOCATION & " -a " & Chr(34) & ZipFileName & Chr(34) & _
" " & Chr(34) & fileToBeZipped & Chr(34), vbNormalFocus
End Sub

sub zips()
Call ZipFile_FX("c:\b.zip", "c:\b.xls")
end sub

Thursday, April 25, 2013

Zip files in a folder

VBA code to zip files in any folder:


Sub Zip_File_Or_Files()
    Dim FSO As New FileSystemObject
    Dim Fld As Folder, Fle As File
    Dim strDate As String, DefPath As String
    Dim oApp As Object
    Dim FileNameZip

    Sheets("Sheet1").Select
    LR = Range("B2").End(xlDown).Row
    For LV = 3 To LR
        DefPath = Range("B3").Value
        FileNameZip = DefPath & "\TestFile" & ".zip"
        NewZip (FileNameZip)
        Set Fld = FSO.GetFolder(DefPath)
        For Each Fle In Fld.Files
            If InStr(1, Fle.Type, "Excel") <> 0 Then
                Set oApp = CreateObject("Shell.Application")
                oApp.Namespace(FileNameZip).CopyHere Fle.Path
                 'Keep script waiting until Compressing is done
                On Error Resume Next
                Application.Wait (Now + TimeValue("0:00:01"))
                On Error GoTo 0
            End If
        Next Fle
    Next LV
End Sub

Sub NewZip(sPath) 'Create empty Zip File
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

Monday, April 22, 2013

List of all Worksheet Names

VBA macro to list down all worksheet names in a workbook:

Sub SheetNames() 
    Columns(1).Insert 
    For i = 1 To Sheets.Count 
        Cells(i, 1) = Sheets(i).Name 
    Next i 
End Sub 

Wednesday, April 17, 2013

Collapsing and expanding of columns

We work on Pivot Tables where we collapse and expand columns. You might come across with situation where you have two pivot tables. What you want to do is when you expand one same column in other pivot table should also expand.

Below code can be used to do that. Put the following code in a module:


Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)  'takes as argument - pt As PivotTable

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(2) 'Mention the sheet name where Pivots are stored

Dim PivotTableIndex As Integer
Dim PivotItemIndex As Integer
Dim PivotFieldIndex As String
Dim BoolValue As Boolean
Dim ItemName As String

Application.ScreenUpdating = False
Application.EnableEvents = False

PivotFieldIndex = "VP" 'Mention column name which you want to collapse/expand

On Error Resume Next


        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail
        ItemName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Put the below code in the active sheet where the pivots are placed:


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Executive_Manager(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Regional_Manager(Target)

End Sub


Saturday, April 13, 2013

Using Tables – Formulas and Pivot Table Summaries

Use the Subtotal() formula to get totals for columns

•Now, just above the table you can add a Subtotal formula for the value in the example to the right
•Type or Select Subtotal(function_num,ref1…)
•function_num will be 9 for Sum
•Press “,” or keyboard or select Ref1 box in the formula editor
•Now select the column of the Table that you want to Subtotal (there should be a small down arrow at the top of this field)
















-------------------------------
Adding a formula in a Table:
•If you have a table with a bunch of Data and want to add a field for Quarter so that we can summarize by quarter the total $.
•Right click the table > Insert > Table Columns to the Left


•Using the Quarter formula you can select the date in the same row and hit Enter on your keyboard
•The formulas will auto fill to the bottom of the table

















-------------------------------
Adding a Pivot Table:
•Select any 1 cell inside the Table
•Insert Ribbon > Pivot Table button
•You will see that the Range of the Pivot Table is the Name of your Table.  By using your Table, if you add any new Fields or Rows to your Table you can hit •Refresh on the Pivot table without having to change the Range if you used a fixed range.
•You now have a Blank Pivot Table
•Field List (right Picture)


















-------------------------------

Customizing your Pivot
Choose Fields to add to report – List of fields available to be added to the Pivot table
Report Filter – Filters  to be applied to the total table
Column Labels – Fields that you will see in the columns (generally I use for Quarters or categories
Row Labels – Fields that you will see in the columns (generally I use for Locations or People)
Values – fields that will have some math applied to them (Sum of, Count of, Average of, etc)

















-------------------------------

Value Field Settings
You can summarize Values in a number of different ways, Sum, Count, Average, Max, Min, etc.
If the Field you placed into Values section of the Pivot table is a number then you can use any option here.  You can place a text type field into Values as well, but will need to use Count
The Show Value As tab has more advanced options.  Some very difficult to understand.  We will look at % of Column Total and % of Row Total as two of the more basic options.











-------------------------------

Calculated Fields
Calculated Fields are fields that go into the Values section of the Pivot Table
Calculated Item are fields that can be added to the Filters, Row Labels, Column Labels
Add Calculated Field (Right Bottom).  With Pivot table selected, PivotTable Tools Ribbon > Options Ribbon > Fields, Items & Sets Button



















-------------------------------


Make your PivotTable Pretty
In the PivotTable Tools Ribbon > Design Ribbon you will see a Layout Section and a Styles Section.  New Color Style (right)
Maybe you like a Tabular Report Layout button (below)











-------------------------------

What if you wanted to turn a Pivot Table into a Table but there are a lot of Blanks in the Sub-Region and Quarter columns

If you have Excel 2010 you can do this through PivotTable Tools Ribbon > Design Ribbon > Report Layout Button > Repeat All Item Label Button
To do it otherwise you can copy the PivotTable and paste the value into a new sheet or empty cells
Highlight the Sub-Region and Quarter columns and rows with (don’t just highlight Column A:B)
Press Ctrl+G on your keyboard
Press Alt+S or click the Special Button
Press K or click on the Blanks bullet
Press Enter or click Ok button


You will see that only the blank cells are now selected
Press = sign and the UP arrow on your keyboard
Press Ctrl+Enter on your Keyboard.  This copies the formula to all the blank cells in the range.
No copy/paste special values and you filled in all the blanks successfully


Insert a Table vs. Range of data


It is best to use a table for several reasons

•Easier to reference
•Easy to add to without having to adjust formula ranges or pivot tables
•Easier to view data because of the auto alternating line colors
•Any formulas added will auto fill to the end of the column

To Convert a range to a Table

•Select Insert Ribbon
•Click the Table Button
•Select your Range if it didn’t do it automatically

Friday, April 12, 2013

Keyboard shortcuts

Sharing some keyboard shortcuts: 
  1. Arrow Keys: Move one cell up, down, left, or right in a worksheet.
  2. Page Down/Page Up: Move one screen down / one screen up in a worksheet.
  3. Alt+Page Down/Alt+Page Up: Move one screen to the right / to the left in a worksheet.
  4. Tab/Shift+Tab: Move one cell to the right / to the left in a worksheet.
  5. Ctrl+Arrow Keys: Move to the edge of next data region (cells that contains data)
  6. Home Move to the beginning of a row in a worksheet.
  7. Ctrl+Home: Move to the beginning of a worksheet.
  8. Ctrl+End: Move to the last cell with content on a worksheet.
  9. Ctrl+f: Display the Find and Replace dialog box (with Find selected).
  10. Ctrl+h: Display the Find and Replace dialog box (with Replace selected).
  11. Shift+F4: Repeat last find.
  12. Ctrl+g (or f5): Display the 'Go To' dialog box.
  13. Ctrl+Arrow Left/Ctrl+Arrow Right: Inside a cell: Move one word to the left / to the right.
  14. Home/End: Inside a cell: Move to the beginning / to the end of a cell entry.
  15. Alt+Arrow Down: Display the AutoComplete list e.g. in cell with dropdowns or autofilter.
  16. End: Turn 'End' mode on. In End mode, press arrow keys to move to the next nonblank cell in the same column or row as the active cell. From here use arrow keys to move by blocks of data, home to move to last cell, or enter to move to the last cell to the right.
More to come ..... 

List files in a folder and sub-folders

Most of the people working in corporate environment deal with large amount data and are aware of the term 'Automation'. In the following discussion am sharing some automation tips and techniques which can be used in day to day work.

People working  on VBA coding in Excel will find this very useful and easy to understand. Below code can be used to list files in a folder and sub-folders.

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

Thursday, April 11, 2013

Importing data into Microsoft Excel using web query

MS Excel provides the facility to import data from excel sources. One way is by using Web Query. It is a very useful feature that Excel has.

To create the Web Query:

  1. Select the first cell in which you want results to appear.
  2. Choose Data | Import External Data | New Web Query to open the dialog box shown in image below.
  3. Enter the URL in Address box and click Go.

Select the table you want to use by using the check mark and click Import button.

It will show the numbers in excel spreadsheet.

Monday, April 8, 2013

Delete number and retain formulas

I have to delete all the number and retain all the formulas in an excel sheet. How to do it?

A. Just follow the below steps:

Excel 2007 version
1. Select the data range; or Press Ctrl+A to select the entire sheet
2. Home tab >> Editing section >> Find & Select >> Constants >> Choose the constants you want to select >> example Numbers, Text etc.
3. Click Ok
4. Press Delete (to remove the selected constants)

Filling blank cells

I want to fill the blank values with the cell value in the above cell, eg. A1 contains IBM, A12 contains HP, A24 contains Cisco etc. So, I want to fill cell values A2:A11 with IBM, A13:A23 with HP etc. This will help me create a flat file from a locked pivot shared by the syndicate research provider. Is there a way to do it without using macros?

A. Yes, it can be done even without using macros. Just follow the below steps:

Select the data range (ex: A1:A24)
Home Tab >> Editing section >> Find & Select >> Go To Special
Choose “Blanks”
Click Ok
Now, the selected cell will be the first blank cell (in this example: A2)
Type “=A1” (i.e. we are linking cell A2 with value in cell A1)
Press Ctrl+ Enter