Excel Find and Highlight Maximum Value from Each Column

this will highlight maximum value from each column in worksheet

Sub Mtest()
Dim c As Range, rng As Range
Dim lCol As Long, LR As Long
With ActiveSheet
    For lCol = 1 To .UsedRange.Columns.Count
      LR = .Cells(.Rows.Count, lCol).End(xlUp).Row
      Set rng = .Range(.Cells(1, lCol), .Cells(LR, lCol))
      For Each c In rng
        If c.Value = Application.WorksheetFunction.Max(rng) Then
          c.Interior.ColorIndex = 3
        Else
          c.Interior.ColorIndex = xlNone
        End If
      Next
    Next lCol
  End With
End Sub

Excel : Open list of website & download data

this requirement came from my friend. He used to open list of websites & download the NAV.
you can download data from web page through web query


Sub Mtest()
Dim page As New InternetExplorer
For i = 2 To Sheets("websitelist").Range("A" & Rows.Count).End(xlUp).Row
URL = Sheets("websitelist").Cells(i, 1)
page.Navigate URL '
page.Visible = True
Do
Loop Until page.ReadyState = READYSTATE_COMPLETE
page.Refresh
'=============================
'call your web query here
'=============================
page.Quit
'if you dont use below line you will get error like
'The remote server machine does not exist or is unavailable
'This quits page and releases the object.
Set page = Nothing
Next i
End Sub

Excel : Login to Gmail /Yahoomail with Vba

Press Alt + F11
Go to tools->References
select below references & click ok

• Microsoft Internet Controls
• Microsoft HTML Object Liabrary

Gmail

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_GMAIL()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "https://www.gmail.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.document
HTMLDoc.all.Email.Value = "abc@gmail.com" 'Enter your gmail id here
HTMLDoc.all.passwd.Value = "abc@1234" 'Enter your password here
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
'Debug.Assert Err = 0
Err.Clear
Resume Next
End If
End Sub

'Open login page of Gmail and go to view source you will get below javascript
'<label>
  '<strong class="email-label">Username</strong>
  '<input type="text" spellcheck="false" name="Email" id="Email" value="">
'</label>
'<label>
  '<strong class="passwd-label">Password</strong>
  '<input type="password" name="Passwd" id="Passwd">
'</label>

Yahoomail


Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_YAHOO()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "https://login.yahoo.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
Do
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.document
HTMLDoc.all.login.Value = "mahesh" 'Enter your yahoo id here
HTMLDoc.all.passwd.Value = "abc" 'Enter your password here
For Each oHTML_Element In HTMLDoc.getElementsByTagName("button")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
'<label for='username'>Yahoo! ID</label>
'<input name='login' id='username' maxlength='96' tabindex='1' value=''>
'<label for='passwd'>Password</label>
'<input name='passwd' id='passwd' type='password' maxlength='64' tabindex='2'>

Excel VBA Learning - (MOS) 2010 Study Guide for Microsoft® Excel

• Excel 2007 VBA Macro Programming 

• Microsoft Office Specialist (MOS) 2010 Study Guide for Microsoft® Excel 

• Vba_for_Excel_Made_Simple_(Made_Simple_Programming)

          

Excel : Create PDF from excel worksheet

you can use below code  for Excel to PDF conversion


Sub CreatePDF()
    Dim wksSheet As Worksheet
    Dim blnFlag As Boolean
    Dim intI As Integer
    Dim intResult As Byte
 
    intI = 0
    intResult = Application.InputBox("Type 1 for Entire Workbook and Type 0 For Active Worksheets")
    If intResult = 0 Then
    Set wksSheet = ActiveSheet
            wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    ThisWorkbook.Path & "\" & wksSheet.Name, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                    False
            Exit Sub
    End If
    For Each wksSheet In ThisWorkbook.Worksheets
        If WorksheetFunction.CountA(wksSheet.Cells) <> 0 Then
            If wksSheet.Visible = xlSheetHidden Then
                wksSheet.Visible = xlSheetVisible
                blnFlag = True
            End If
            wksSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                    ThisWorkbook.Path & "\" & wksSheet.Name, Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
                    False
                    intI = intI + 1
            If blnFlag = True Then
            wksSheet.Visible = xlSheetHidden
            blnFlag = False
            End If
        End If
    Next
    MsgBox intI & " Worksheet(s) has been Exported to PDF", vbInformation
   End Sub

Excel : Hack/download Greetings (swf file) from web & Embedding in excel

I found this is best way to wish your friend on any occasion
Most company block certain web sites so even you send them E Greeting card they may not able to view

Hack/download Greetings (swf file ) from http://www.123greetings.com/
1. Go to url http://www.123greetings.com/
2. click on the e card which you want to download

3. Press Ctrl + U or Right click on web page & select view page source
4. Press Ctrl + F & find swf
5. find the url of swf file as shown in below image

4.  Copy the url to new browser window
5.  Press ctrl + S

6. Save the web page. it will save the file in swf ( Shockwave Flash Object ) format
7. Further you can convert this file to any video format using software
  http://www.dvdvideosoft.com/products/
8. You can insert this file in excel as well
 http://excelvbaandmacros.blogspot.com/2011/12/excel-why-this-kolaveri-diembed.html

Excel : Delete Blank Rows & Column from worksheet

Try Below :


Sub DeleteBlankRows()
    Dim Rw As Long, RwCnt As Long, Rng As Range
   
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
On Error GoTo Exits:
   
    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        'Set Rng = Range("A1:B6")
     Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
    End If
    RwCnt = 0
    For Rw = Rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
            Rng.Rows(Rw).EntireRow.Delete
            RwCnt = RwCnt + 1
        End If
    Next Rw
   
Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

Sub DeleteBlankColumns()
    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("A1:B6")
        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
    End If
    ColCnt = 0
    For Col = Rng.Columns.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then
            Rng.Columns(Col).EntireColumn.Delete
            ColCnt = ColCnt + 1
        End If
    Next Col
   
Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub