Posts by manish_sharma

Hi everyone,

Could some one advise me how can I make the macro below work faster for me. I am not an expert with macro and found this on internet and modified it to my requirement. Basically what I am trying to do is if the cell value in column C and D is 0, I want those rows hidden. I have 50,000 rows in the sheet. Macro takes about 2-3 minutes to run. Is there any way I can make it quicker.

Sub HideZeroRowsBS1()
Dim lstRw, nxtRw As Long
'Disable Screenupdating
 Application.ScreenUpdating = False
'Determine last Row that contains data:
  With Sheet59
   lstRw = .Range("A" & Rows.Count).End(xlUp).Row
'Unhide all rows
   .Rows("2:" & lstRw).Hidden = False
'Loop through rows
     For nxtRw = 2 To lstRw
'Hide rows where G:S Sum to 0
      If WorksheetFunction.Sum(.Range("C" & nxtRw), _
                               .Range("D" & nxtRw)) = 0 Then
        .Rows(nxtRw).Hidden = True
      End If
     Next
  End With
'Enable Screenupdating
 Application.ScreenUpdating = True
 End Sub

Any help with this will be appreciated. Also is it possible to change this macro in a way that I don't have to specify a sheet number, instead it just takes the active sheet. FYI, my range is fixed that I want this macro to look at starting from A2:D50003. Row 1 is heading. I have noticed that if I add any extra column after column D, it makes macro go even slower. Thanks in advance.

Manish

Re: Macro: Save File & Automatically Overwrite Old File

Thanks gmccreedy

That's perfect..it works like a charm...thank you

Can you tell me what's the best way to learn macros...I love what they can do and I want to be expert as well..

Highly appreciated

Hi Everyone,

I need some help with the macro below

Sub SaveIt()
     
    Dim strFirst As String, strLast As String
     
    ChDir "C:\Documents and Settings\sharmam\Desktop\Test Auto Save"
'*****************
    Application.DisplayAlerts = False
'*****************
    With Worksheets(1)
        strFirst = .Range("C4")
        strLast = .Range("C6")
    End With
     
    ThisWorkbook.SaveAs _
    "C:\Documents and Settings\sharmam\Desktop\Test Auto Save" & _
    strFirst & " -Capex- " & strLast, xlWorkbookNormal
'*****************
    Application.DisplayAlerts = True
'*****************
End Sub

In the above macro, instead of saying my username "sharmam",what can I do so the filepath automatically picks up the current username who is logged on to the computer. The file that has this macro will be available on company intranet and I want user to save the file on their desktop first before starting to use it.

Any help with this would be highly appreciated. FYI, I am a beginner with macro and I copied the above from somewhere else but it works great.

many thanks

Hi SMC,

Yes, I got your reply and posted a thanks message but for some reason did not appear. This macro works great. Thanks a lot for this. Much appreciated.

Hi All,

How can I force users to enable macros on an excel file that they will open from a company Intranet page. Any help would be greatly appreciated.

Manish...beginner

Hi Experts,

My first day at this forum as I really need help with this. I am brand new to macros/VBA so please be kind. your help/suggestions will be highly appreciated. I want to have a macro that should do the following:

1) It should not allow users to print the worksheet except if it's done through this macro

2) Check if certain cell (such as A3, B10 and C23) are filled (not empty).

3) If empty, does not allow print, if filled, print the work sheet.

Is this possible or I am dreaming. Please help...much appreciated.

Manish