Vikas Verma's blog

Vikas Verma's picture

TRANSPOSING WITH FORMULA

Hi,

Please try this to transpose the vertical values to horizontal Values.

=IFERROR(OFFSET(INDIRECT("A"&SMALL(IF((ISBLANK($A$1:$A$35))*ROW($A$1:$A$35)>0,ROW($A$1:$A$35)),ROW($A1))),COLUMN(A1),0),"")

with CRTL+SHIFT+ENTER

Thanks

Vikas Verma's picture

How to reduce excel file size?

Tips:

1. Tips & Tricks on How to reduce Excel file size?
2. Are you thinking why my Excel file size is so bigand how can I reduce it?
3. Don’t Worry…..Here you will find great Tips on Reducing Excel File size
4. Tip 1. Zip the Excel file
5. Tip 2: Save the Excel file in .xlsbformat
6. Tip 3. Remove unused cellsFollow below steps to remove unused cells1. Select any cell and press F5.2. Click Special-> Blank radio button.3. Go to Edit-> Clear All.
7. Tip 4. Remove formattingFollow below steps to remove formatting1. Open your Excel sheet.2. Press CTRL+A3. Click Remove Formats.

Vikas Verma's picture

CELLS Property in VBA

CELLS Property....

This property can be used as an alternative to the absolute range property and is generally more flexible to work with, as variables are easier to pass into it.
There are two optional arguments:
Cells([row] [,column])
Leaving the arguments empty (no brackets), it will detect the current selection as the active range.
Adding an argument to either row or column with a number will refer to the co-ordination of the number passed.
Adding both arguments will explicitly locate the single cell’s co-ordinate.
'Examples of the Cells property
Sub CellsExample()

Vikas Verma's picture

Soul of Excel formulas

Hi if you want to be an expert in excel formulas then learn Cell Referencing.....

Thanks

Cell Referencing
Vikas Verma's picture

Move ALL files (or of a specific file type) from one folder into another folder

Sub MoveFilesFolder2Folder()
Dim fso
Dim sfol As String, dfol As String

sfol = "c:\MyFolder" ' change to match the source folder path
dfol = "e:\MyFolder" ' change to match the destination folder path

Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next

If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else

Vikas Verma's picture

A bunch of tips for good programming

A bunch of tips for good programming.

1) First analyze the problem clearly

2) Next step is to think twice about how to solve that problem

3) Gather complete requirements. Take the time to write down what goals the end product needs to achieve, and who your user base will be. Clarity of thought at this stage will save a lot of time down the line.

4) Write a thorough implementation plan (or model).
For something small and self-contained, this might be just a basic flowchart or a simple equation.

Vikas Verma's picture

Consolidate Sub-Folder files with a click

Syndicate content