admin - 3/3 - Houston Computer Classes

VBA – Letter Count for Each Alphabet Character – Excel Macro

April 16, 2015 in Excel, Reference, Tutorial /by admin

This Excel Macro will go through all the cells in column A and output the count for each letter in column B. This is for English alphabet only and it will skip all the other characters and spaces.

<br />
Sub LetterCount()</p>
<p>Dim ws As Worksheet<br />
Set ws = ActiveSheet</p>
<p>lastrow = Cells(Rows.Count, 1).End(xlUp).Row</p>
<p>rg = Range("a1:a" &amp; lastrow)<br />
i = 1</p>
<p>For c = 97 To 122</p>
<p>cnt = 0</p>
<p>    For Each cell In rg</p>
<p>    lth = Len(cell)<br />
        For lt = 1 To lth</p>
<p>            chktext = Mid(cell, lt, 1)</p>
<p>              If chktext = Chr(c) Then</p>
<p>                cnt = cnt + 1<br />
            End If</p>
<p>        Next lt</p>
<p>    Next cell</p>
<p>    If cnt &gt; 0 Then<br />
        ws.Cells(i, 2) = Chr(c) &amp; " = " &amp; cnt<br />
        i = i + 1<br />
    End If</p>
<p>Next c</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

Sub LetterCount()

Dim ws As Worksheet

Set ws = ActiveSheet

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

rg = Range("a1:a" & lastrow)

i = 1

For c = 97 To 122

cnt = 0

For Each cell In rg

lth = Len(cell)

For lt = 1 To lth

chktext = Mid(cell, lt, 1)

If chktext = Chr(c) Then

cnt = cnt + 1

End If

Next lt

Next cell

If cnt > 0 Then

ws.Cells(i, 2) = Chr(c) & " = " & cnt

i = i + 1

End If

Next c

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

Excel VBA – Export Each Worksheet to a Separate PDF – Macro

March 25, 2015 in Excel, Reference, Tutorial /by admin

If you need to Export Each sheet to an individual .pdf file this Macro will do it for you. It will go through all the sheets in your Workbook and save each one to a separate PDF file using the worksheet name as file name.

<br />
Sub ExportToPDFs()<br />
' PDF Export Macro<br />
' Change C:\Exports\ to your folder path where you need the diles saved<br />
' Save Each Worksheet to a separate PDF file.</p>
<p>Dim ws As Worksheet</p>
<p>For Each ws In Worksheets<br />
ws.Select<br />
nm = ws.Name</p>
<p>ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _<br />
Filename:="C:\Exports\" & nm & ".pdf", _<br />
Quality:=xlQualityStandard, IncludeDocProperties:=True, _<br />
IgnorePrintAreas:=False, OpenAfterPublish:=False</p>
<p>Next ws</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

Sub ExportToPDFs()

' PDF Export Macro

' Change C:\Exports\ to your folder path where you need the diles saved

' Save Each Worksheet to a separate PDF file.

Dim ws As Worksheet

For Each ws In Worksheets

ws.Select

nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _

Filename:="C:\Exports\" & nm & ".pdf", _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

Excel – Age Calculation – How to Get Age from Date of Birth (DOB)

March 17, 2015 in Excel, Reference, Tutorial /by admin

The easiest way to calculate person’s age from using their birthday in Excel is by utilizing undocumented DATEDIF function. DATEDIF is a built-in Excel function, however, unlike all the other built-in functions it will not auto-populate, show up in auto-complete or give tooltips on the function. So how do you use DATEDIF function? The syntax for the function is […]

Average City of Chicago Income Per Capita – Broken Down by Neighborhoods

October 30, 2014 in News /by admin

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014 Average of PER CAPITA INCOME $25,597.00 COMMUNITY AREA NAME Average Near North Side $88,669.00 Lincoln Park $71,551.00 Loop $65,526.00 Lake View $60,058.00 Near South Side $59,077.00 North Center $57,123.00 Near West Side $44,689.00 Forest Glen $44,164.00 West Town $43,198.00 […]

Average City of Chicago Employee Salary – What Do City Employees Really Make?

October 30, 2014 in News /by admin

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014 Average of Employee Annual Salary $75,559.11 by Department DoIT $94,083.53 BUILDINGS $91,814.51 FIRE $88,247.96 MAYOR’S OFFICE $86,765.19 HUMAN RELATIONS $85,095.33 BUDGET & MGMT $84,022.27 IPRA $82,412.05 BOARD OF ETHICS $82,334.67 WATER MGMNT $82,287.46 COMMUNITY DEVELOPMENT $81,502.14 GENERAL SERVICES $80,859.86 […]

Add a New WorkSheet in Excel – VBA Macro Code

June 13, 2014 in Excel, Learn /by admin

This VBA code will add a new worksheet in your Excel project.

<br />
Sub AddNewWorksheet()</p>
<p> Dim NewWorkSheet As Worksheet<br />
 Set NewWorkSheet = Sheets.Add</p>
<p>End Sub

1

2

3

4

5

6

Sub AddNewWorksheet()

Dim NewWorkSheet As Worksheet

Set NewWorkSheet = Sheets.Add

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

Convert CrossTab Table to Database List Table – Excel VBA Macro Code

June 13, 2014 in Excel, Learn, Office /by admin

If you work with databases and regular data tables you probably had a situation when someone gave you a regular Excel 2 dimensional spreadsheet that is not that useful if you need to use the data within a database and run some queries or just make PivotTables in Excel. The Macro code below will transform […]

Delete All Hidden Columns and Rows – VBA Excel – Code

June 12, 2014 in Excel, Learn /by admin

This VBA code can help you delete all hidden columns and rows from your excel sheet with a click of a button.

<br />
<em>Sub hiddendelete()</em><br />
<em>For lp = 256 To 1 Step -1 'loop through all columns</em><br />
<em>If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else</em><br />
<em>Next</em><br />
<em>For lp = 65536 To 1 Step -1 'loop through all rows</em><br />
<em>If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else</em><br />
<em>Next</em><br />
<em>End Sub</em>

1

2

3

4

5

6

7

8

Sub hiddendelete()

For lp = 256 To 1 Step -1 'loop through all columns

If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else

Next

For lp = 65536 To 1 Step -1 'loop through all rows

If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else

Next

End Sub

About admin

Entries by admin

VBA – Letter Count for Each Alphabet Character – Excel Macro

Excel VBA – Export Each Worksheet to a Separate PDF – Macro

Excel – Age Calculation – How to Get Age from Date of Birth (DOB)

Average City of Chicago Income Per Capita – Broken Down by Neighborhoods

Average City of Chicago Employee Salary – What Do City Employees Really Make?

Add a New WorkSheet in Excel – VBA Macro Code

Convert CrossTab Table to Database List Table – Excel VBA Macro Code

Delete All Hidden Columns and Rows – VBA Excel – Code

Locations:

Author Archive for: admin

About admin

Entries by admin

Locations: