Posts

Showing posts with the label text to speech

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…

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…

Excel count non-empty cells or blank cells

Excel provides a function to check blank cells and also provide functions to check cells that are not empty.

CountA function will check cells that are not empty.
Countblank function as its name literally implies will check or count for blank or empty cells.

Example for Countblank function:

=COUNTBLANK(A1:B11) --- Function will check for empty spaces from A1 to B11 (2 columns)
=COUNTBLANK((A1:A11)) --- Function will check for empty spaces in a single column. Note the double parentheses
Example for CountA function:
=COUNTA(A1:A11) --- Function to check cells with values in a single column (Column A only)
=COUNTA(A1:B11)  --- Functio to check cells with values in two columns from A1 to B11 (Column A and B)

To enter formula above using VBA:
==============================
Sub VBA_Formula()
Dim Cell_Formula As String Dim xvalue As String Cell_Formula = "=COUNTA(A1:A11)" 'Cell_Formula = "=COUNTBLANK((A1:A11))" 'Cell_Formula = "=COUNTBLANK(A1:B11)"
'Set cell for…

Excel VBA autofill date range

Image
How to auto fill a range of cells with date?

To auto fill range of cells can be done via VBA or manually.

To manually fill a range of cells with date.

Type two dates on two cells and highlight the two cells and manually drag to the desired range of cells.

Drag from the bottom right with the "+" sign on it.

See screen shot below:



To do it via VBA is quite simple also using autofill function.

Sub AutoFill_Dates()

  Dim srcRange As Range
  Dim destRange As Range

  Set srcRange = ActiveSheet.Range("E1") 'E1 should have valid date value
  Set destRange = ActiveSheet.Range("E1:E15") 'E1 to E15 will be filled with the dates (starting the date specified on E1)
  srcRange.AutoFill destRange, xlFillSeries 'xlFillSeries will auto fill the dates in sequence

End Sub


Cheers.. Hope it helps..

Excel VBA lock cell values

Excel VBA to lock cell values but allowing user to copy the cell value.

Locking a cell or protecting a sheet will lock the cells and make it as read only and user also has  to remember the password.

So if the user forgot the password, whatever values on the cell or cells needs to type or do again  the worksheet.

VBA code below will lock the cell value allowing user to copy the value or even modify it but the moment the cell is not active anymore, then VBA will put back whatever values that is on the code.

Even if the macro has a password, the user don't need to type or the worksheet. Since the user will be able to copy the value.

To protect a range, just adjust  the range value.

=====================
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
    Application.EnableEvents = False

 If Range("B1") <> "LA Lakers Simply the best" Then
    Cells(1, 2).Value = "LA Lakers Simply the…

Text to speech using Microsoft Word

Image
Enable text to speech using Microsoft Word 2010 and newer version.
This guide below will enable text to speech of Microsoft Word which is partly hidden deep on the Word options.
This guide will be the same procedure on how to enable this option on Outlook, Power Point and Excel.
If you feel tired of reading, but need to read a particular document.
Well, let the Word read it for you. 
Sometimes listening is much better than reading.
If you disagree, then just keep it to yourself. As ice cream has many flavors, different persons from different walk of life have also its own set of liking.
Anyway, once you purchase a Microsoft Office. It would be better to make use this feature since you had paid for it.
Screen shot below was taken using Microsoft Word 2010.
Click on “File” and on the drop down menu, click on “options”.


 Then a new window will open. 
Click on "Customize Ribbon".


Click on the small "drop down arrow",  VBA programmers call it combo box. Android programmer…