Posts

Showing posts with the label VBA

Excel find last row with value in a specific column

1. How to find last row with value using vba in a specific column?

2. How to find last row with value  using vba in a worksheet?

The questions above are entirely two different scenarios.

The first question will find the last row with value in a specific column within the worksheet.

While the second question find the last row with value of the entire worksheet.

The first question is applicable if you need to find the last row in different columns.

Let's say Column 1 or Column A, or Column 2 or Column B etc.

Example code below will find the last row in a specific column via VBA.

===================
'command Cells(Rows.Count, 1) = find the last row with value in Column 1 for Sheet3

Dim last_row As Integer

last_row = Worksheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row

Msgbox last_row

'replace 1 with the desired column 
'ex: command Cells(Rows.Count, 7) = find the last row with value in Column 7
===================

If you don't need to specify a worksheet or a sp…

How to insert landscape orientation in Word?

Image
If you are writing a thesis, a report or an article that you need to insert a landscape layout in a specific page in Word document but your orientation all long is portrait and changing the orientation causes the whole layout to change.
Inserting landscape in a specific page in a word document can be done by inserting section breaks. Let’s just make a basic example.
Let’s say you have 3 page document and you want that first page in Portrait mode, second page in landscape and third page in portrait layout also.
First, click on “home” tab, click the “show/hide paragraph marks” option or see the icon below. Enabling this option the section breaks, spaces and tabs will be visible. But what we are interested is to see the “section breaks”.


In the first page position the cursor on the last area at the bottom of the page.
In Word 2010, click on “page layout” in the ribbon tab. Click on “breaks” and select “next page”. In the second page, position also the cursor at the middle or bottom of t…

Create a progress bar in Excel

Image
Create a progress bar in Excel that varies on the input value and range.

If the value is  equivalent to 100% or maximum input is reach then the color will fill the whole cell.

This example below was created using Excel 2010, the logic should be the same with other version that supports this function.

1. Select the cell, that will have the progress bar.
    Click on "Home" tab, click on "Conditional Formatting"
    - In drop down menu select Data Bars
       - In the sub menu click "More Rules".

See screen shot below:



2. After clicking "More Rules", "New Formatting Rules" window will open.
    - In "Select a Rule Type"
       "Format all cells based on their values" should be selected
    - Under Rule Description
       Set the type to "number"
       Set the range of minimum and maximum value
       Select the color that you want and click "OK", once customization is done.

See screen shot below:





Excel formula fixed cell address

How to insert a formula with a fixed cell address? Absolute referencing will come to the rescue.

Absolute reference in Excel is prefix with a dollar ($) sign.

$A$1: This means that Column "A" and Row "1" will not change when copied to another cell.

Example:

=SUM(A$1,$A2,$A$1)


The above formula when copied from one cell to another cell, $A$1 will remain the same.

While A$1,$A2 will change its location when copied to another cell.

 A$1: This means that column "A" will change while row location (which is number 1) will not change when copied.

 So this absolute reference will change like D$1, G$1 or E$1 depends on the location. So number "1" value will never change.

 $A1: The column "A" will not change when copied from one cell to another cell, while the row value which is number "1" will change.


So the above absolute reference will change like $A3, $A10, $A12 etc., Column "A" will be constant while the row changes.


So…

Word VBA Select Page

How to select a page in Word VBA?

Sub Select_Home()

'Select the first page of the document (page 1)
'Like pressing Ctrl + Home

Selection.GoTo wdGoToBookmark, , , "\StartOfDoc"


End Sub

Sub Select_document_end()

'Select the end of the document
'Like pressing "Ctrl+End"

Selection.GoTo wdGoToBookmark, , , "\EndOfDoc"

End Sub

Sub Select_via_Page_Number()

'specify the page number
'Example below will select page 5

Selection.GoTo wdGoToBookmark, , , "5"

End Sub

Tested and working using Word 2010.

Cheers..till next time :) Happy coding.

================================
Free Android Apps:

Click on links below to find out more:

Linux Android App cheat sheet:
https://play.google.com/store/apps/details?id=com.LinuxMobileKit


https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer

Catholic Rosary Guide  for Android:

https://play.google.com/store/apps/details?id=com.myrosaryapp

http://quickbytesstuff.blogspot.sg/2014/09/how-to-r…

Text file compare using PowerShell

Outlook VBA get address book names

How to get Outlook address book names via VBA?
Outlook has the option to create multiple address books. Of course, it's for a good reason, to organize address books.
Well, why need to organize? I guess the reason will depend on every individual whether it's a thing to practice or a thing to be ignored.
Outlook address book can be set to manage contacts.
For example, you can have an address book just for Personal use which includes friends and family.
You can set the address book for vendors and another address book for customers only and of course contacts within the organization.
Organizing is a skill if not an attitude to make work easier and manageable.
VBA code below will get all the address book names on Outlook, and once the address book is known you can expand the code to get contacts for each specific address book by supplying the address book name.
====================================== Sub getfoldercontact_addressbook_names()


Dim mContact As AddressList
Dim mAddressBook…

PowerShell split and join string

How to split a string by space using PowerShell?
PowerShell provides an easy way to do it using "split" method in PowerShell.
The split method accepts any delimiter; it can be space, dash, comma or other characters as long as it has a pattern on the string.
The split method will literally split the string and the output is displayed on a separate line or separated via a line feed.
Example:
$string = "This is my test string"
$split_string_array = $string -split " "
$split_string_array
Output will be: This
is
my
test
string
If there is a need to remove the spaces but combine the string in one line, PowerShell also provides the "join" method which basically combine or joins the string in one line.
Example: $string = "This is my test string"
$split_string_array = $string -split " "
$join_string_array = $split_string_array -Join ""
Write-Host $join_string_array
Output: Thisismyteststring
Using the join method, a string can be manipu…

PowerShell Get Registry Values or Entries

Navigating the registry is sometimes annoying you need to click here and there.
With the use of PowerShell life can be easier, provided you know exactly the complete registry path.
Below is a PowerShell cmdlet that will list all the software that will run automatically on the computer for all users.
Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run

The PowerShell cmdlet below will list the software that will run automatically for the currently logged on user in which the command is executed.
Get-ItemProperty -Path Registry::HKCU\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
Sample Output:

Akamai NetSession Interface : "C:\Users\User_007_JB\AppData\Local\Akamai\netsession_win.exe" ISUSPM: "C:\ProgramData\FLEXnet\Connect\11\ISUSPM.exe" -scheduler Sidebar: C:\Program Files\Windows Sidebar\sidebar.exe /autoRun LightScribe Control Panel: C:\Program Files\Common Files\LightScribe\LightScribeControlPanel.exe -hidden PSPath: Micros…

List all files with a particular extension using PowerShell

One liner command to list files with a specific extension in a given path.

Command below will list all the file that will match and display it's file name, file size and the directory where the file is located or found.

The output of PowerShell is redirected to a text file.


$FileExtension=".key"

$FilesDir = get-childitem "d:\CertKeys" -recurse | where {$_.extension -eq $FileExtension } | format-table name,length,Directory -AutoSize

$FilesDir | out-file "d:\certkey.txt"


Or save some few bytes by using the shorter form of the cmdlets.

$FileExtension=".key"

$FilesDir = gci "d:\CertKeys" -recurse | ? {$_.extension -eq $FileExtension } | ft name,length,Directory -AutoSize

$FilesDir | out-file "d:\certkey.txt"


Sample Output:
Name                          length         Directory 
----                              ------          --------- 
ssl.key
ca.key                        963    D:\CertKeys\apache\ssl.key 
server.key        …

Set IP Address using Netsh and PowerShell

How to set IP Address of the remote computer using PowerShell?
How to set DNS IP Address of the remote computer using PowerShell?
How to set static IP and DNS address in local or remote computer via PowerShell?

Script below will set local IP or set remote IP Address using PowerShell with the help of Netsh command.

For the script to work Winrm or Windows Remote Management must be enabled either on the local PC or remote computer or server.
The script of course needs to run at an elevated mode with the proper rights or privileges or in simple words the script will work only if you have the administrator rights.

Here’s the script:
#===============================
$set_IP= { netshinterfaceipsetaddress"Wi-Fi"static192.168.1.107255.255.255.0192.168.1.254}
$set_DNS1= {netshinterfaceipadddns"Wi-Fi"8.8.8.8 }
$SET_DNS2= { netshinterfaceipadddns"Wi-Fi"4.2.2.2index=2  }
$show_ipconfig= { ipconfig/all }

Invoke-Command-ScriptBlock$set_IP-ComputerName. Invoke-Command-ScriptBl…