Fill Blank Cells - Inbuilt option in Excel

How to fill blank cells in a data range (A1:A20) with out copy paste option in simple steps?

1. Select the complete data set range (A1:A20).

2. Use CTRL + G (goto window) then, hit ALT + S and select Blanks or type 'K'.

3. Click on 'Ok' or press 'Enter'.

4. Now, all of the blank cells have been selected.

5. Now the active cell is the first blank cell in the data set.

6. Type '=' and press 'UP' arrow and press CTRL + Enter, excel will copy the respective formula to all blank cells.

7. Again select the complete data set range to copy and paste the values from formulas. The filled contents are formulas, and we need to convert the formulas to values.

8. Copy the data set and press Alt + E + S (or) Ctrl + Alt + V to enable paste special method, and select values and press enter to paste values.

9. Now the formulas are converted to values.

How to Paste MS Excel sheet data without losing its Format in Gmail?

When you copy a range of cells from Excel, you are only copying data. No HTML is copied, as Excel doesn't generate it.

The grid lines are non-printing in any case, so you need first to turn on cell borders in Excel for any grid lines you want to appear. You then need to copy the selection into a program that understands Excel formatting and can show the grid lines and colors on screen.

So select your cell range and copy-paste into a Word document. Word will generate the HTML code required to display the table, and you can then copy-paste from Word into a Gmail compose message.

It's an extra step, but works every time AND should be received correctly by any recipient.

Trim all cells values in entire worksheet using VBA

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160.

This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.

We get data from many sources like web pages, databases, software outputs and many more. Copying a webpage data to excel, will have non breakable space characters, linefeed characters and carriage return characters in any portion of a sentence. 

Using Trim function alone we can not remove that characters. It only removes leading and trailing spaces from the text.

Here is a solution to remove that unwanted characters. If data has all the non breakable space characters, linefeed characters and carriage return characters, this subroutine will converts all the above said characters to normal space character. After converting to normal space character it will execute Trim function.

Trim - Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax:

Function Trim(String)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Sub TrimCellsEntireWorksheet() 'Assigning # of rows and columns to trim intTotalRows = Cells.SpecialCells(xlLastCell).Row intTotalCols = Cells.SpecialCells(xlLastCell).Column For rwIndex = 2 To intTotalRows 'First row is header, so here ignore the header row For colIndex = 1 To intTotalCols 'Replaces Non-Breakable Space Character (Char(160)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, Chr(160)) Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, Chr(160), " ") Loop 'Replaces Linefeed Character (Chr(10)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "", " ") Loop 'Replaces Carriage Return Character (Chr(13)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "", " ") Loop 'Replaces Double Spaces " " to Single Space " " Do While InStr(Cells(rwIndex, colIndex).Value, " ") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, " ", " ") Loop 'Removes Leading and Trailing spaces from the text Cells(rwIndex, colIndex).Value = Trim(Cells(rwIndex, colIndex).Value) Next colIndex Next rwIndex MsgBox "Trimming of Cell values in entire worksheet is completed", , "Trim - Cell Values on entire worksheet" End Sub