Delete Blank Columns From The Excel

-

Following VBA Code will delete blank columns by ignoring 1st row (i.e header) in the excel


Steps to follow

1. Open New Excel Workbook

2. Press Alt+F11

3 Click Insert-->Modue

4. Copy and paste the below code in the Blank module inserted from above step 3

5. Click on the Save button or press Ctrl+S

6. Save the excel workbook in ".xlsm" OR "Macro-Enabled Workbook" format.

How to Use this Macro-Enabled WorkBook

1. Open the Macro-Enabled Workbook (eg: "delete_column.xlsm")

2. Copy-paste the data (including header) which contains blank columns.

Note: If your data doesn't have a header row then replace the following line of code

If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) =0 Then

3. Navigate to View in the Menu Bar

4. Select drop down from the Macros Icon--> View Macros

5. Ensure macro name "DeleteROPBlanks" is selected and macros in selected as (eg: "delete_column.xlsm")

6. Hit Run button

7. You can observe the blank columns are deleted.


 Sub DeleteROPBlanks()

'

' DeleteROPBlanks Macro

Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then

Set Rng = Selection

Else

Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))

End If

ColCnt = 0

For Col = Rng.Columns.Count To 2 Step -1

If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) < 2 Then

Rng.Columns(Col).EntireColumn.Delete

ColCnt = ColCnt + 1

End If

Next Col

Exits:

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

Comments

Post a Comment

Popular posts from this blog

Steps to Deploy the OFSAA application in WEBLOGIC server:

-
Steps to Deploy the OFSAA application in WEBLOGIC server: ========================================================= 1) Generate the "context_name.ear" from the FIC_WEB area by executing the ./ant.sh 2) Create a folder by the name "applications" within the domain directory 3) Create a folder by the name "context_name.ear" within the newly created "applications" folder 4) Copy the context_name.ear file from the FIC_WEB folder to the newly create the "context_name.ear" folder created within the applciations folder 5) Explode the .ear file copied from the FIC_WEB area (jar -xvf context_name.ear), There are 2 files and 1 directory created after the .ear file is exploded as mentioned below a) context_name.war ---> file b) context_name.ear ---> file c) META_INF ---> folder 6) Delete the above files (.ear and .war files) 7) Create a new folder by the name "context_name.war" 8) Copy the context_name.war file from th...
Read more
-
1) How do you decide the defect prone area ? 2) What do you do when you have resources with low competency ? 3) Example of risk and how to mitigate it ? 4) What do you do to increase teams productivity ? 5) What is effort estimation process in your company ?
Read more

Client / Server Applications

-
What is: The client–server characteristic describes the relationship of cooperating programs in an application. The server component provides a function or service to one or many clients, which initiate requests for such services. Advantages: * In most cases, a client–server architecture enables the roles and responsibilities of a computing system to be distributed among several independent computers that are known to each other only through a network. This creates an additional advantage to this architecture: greater ease of maintenance. For example, it is possible to replace, repair, upgrade, or even relocate a server while its clients remain both unaware and unaffected by that change. * All data is stored on the servers, which generally have far greater security controls than most clients. Servers can better control access and resources, to guarantee that only those clients with the appropriate permissions may access and change data. * Since data storage is centralized, update...
Read more