Posts by Keithcornett

Re: Automatically hide rows when cell value =0

It worked perfectly! Thanks so much for you help. This is absolutely the best web site for VBA and Excel help. You guy's are AWESOME!

Re: Automatically hide rows when cell value =0

Hi Tom,

Thanks for your help on this. I changed the column selected to C:C since the "grand total" is in that column, but when I ran the macro, it didn't hide the rows with a zero value. Since I'm not exactly proficient with VBA (okay, more like downright illiterate) I've attached a sample spreadsheet with the macro you sent. I just want to hide the rows where the charts column has a zero value. Again, I really appreciate your help.

Keith

Is there a way to write a macro that would hide a row if the value of a particular cell in that row = 0? I would need this to loop until the words "grand total" are found. Thanks for any help on this.

Worked perfectly for me too!

Thanks for all your help Andy

Hi Andy,

I tried it, but I'm still getting the prompt.

I recorded a macro but whenever I run it, it always stops during execution and prompts for a yes/no request on whether I want to delete the worksheet.

I was wondering if there is a way to automatically select "yes" when the pop up box asks if I want to permanently delete a worksheet so the macro will continue to run without stopping.

Here's a copy of what I'm using. Thanks for any help.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/2/2003 by Keith C
'
' Keyboard Shortcut: Ctrl+q
'
    Workbooks.OpenText Filename:="C:\My documents\AB75\C37.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
        1), Array(7, 1), Array(40, 1), Array(56, 1), Array(78, 1), Array(97, 1), Array(116, 1), _
        Array(126, 1))
    Sheets("C37").Select
    Application.CutCopyMode = False
    Sheets("C37").Move After:=Workbooks("C37.xls").Sheets(3)
    Cells.Select
    Sheets("C37").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Raw Data").Select
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    Cells.EntireColumn.AutoFit
        Columns("D:E").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Range("C2:J227").Select
    Selection.Cut
    Range("C1").Select
    ActiveSheet.Paste
    Range("C6").Select
    Sheets("raw data").Select
    Cells.Select
    Selection.Copy
    Sheets("Import").Select
    ActiveSheet.Paste
    Sheets("Import").Select
    Range("A1").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    Sheets("C37").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Data").Select
End Sub

You might try opening up a new excel spreadsheet and use a formula to link the cells to the corrupted file and extract the data this way.

Hi Bill,

Just one more question. Does the networkdays formula also account for holidays too?

Thanks again for all your help.

Keith

Thanks for the upgrade Bill. I like the formulas. You've definitely made my life simpler.

Keith

Bill, you are a genius!!! Thanks so much! The formulas work perfectly.

Hi Bill,

This looks great, but when I changed the dates in cells F9 through F13, I got a #NAME? error message in cells J9 through J13. Also, I tried to enter the =NETWORKDAYS formula in my worksheet and it did not work and I didn't have it in my formula palate. Is this an add in?

Thanks again for your help.

I need a rather complex formula that will calculate how many workdays have occurred thirty days from the date in column "F" on the attached worksheet.

Column A is the date, Column B is the weekday and Column C contains a "1" for workday or "0" for weekend/holiday.

I'm trying to calculate a projection as to where we should be in terms of a daily target.

Thanks for any help.

Thank you~! It worked perfectly.... You are absolutely brilliant!!!

I need a nested IF formula that will basically tell me if certain cells have data in them when others don't, for example:

If cell A1 is blank, leave blank
If cell A1 contains data and cell B1 contains data, then leave blank
If cell A1 contains data and cell B1 is blank, then show a "1"

I tried this =if(A1="","",if(A1<>""&B1="",1,"")) but it didn't seem to work.

Thanks for any help.

Keith

Thanks Domenic! I cleared the cell, checked the formatting (it was already set for general), but re-formatted and it seems to work.

I appreciate your help.

I enter a formula in a cell and the cell shows the formula as text. The formula is valid, the "view formulas" option is turned off. Don't know if there is some sort of hidden formatting that is causing this. Other formulas on the worksheet are functioning, but as soon as I enter the cell and make any modifications, the formula shows up as text again.

Thanks for any help.

Hi Tom,

Thanks for your help. I tried the macro but it didn't seem to work. There are formulas in the blank cells and I thought that might be a problem, but I tried copying the worksheet, pasting it as a value to eliminate the formulas and ran the macro again without any luck.

The first 10 or 20 rows do not have any data showing (other than the formulas) then its sporadic afterwards.

Hi,

I've got a worksheet that is linked to another with formulas that extract data meeting certain criteria. Unfortunately, this leaves a lot of blank rows in the worksheet. Is there a macro that would delete the empty rows so all the data will be together and start at the top of the page?

Thanks for any help.

Thank You

The abbreviated version works perfect! Thanks again so much for your help and the quick response. You guy's are awesome!!!

I have a file that has a persons name in a cell as follows: John A. Smith, M.D. I need the name to read last name, first name middle initial i.e. "Smith, John A" with the M.D. or D.O. dropped. The problem I'm having is that not all names have middle initials and some names are hyphenated. Listed below are how the names are and how I would like them to read. Thanks for any help. Is there a formula that would do the trick?

Melvin A. Ochs, M.D. Ochs, Melvin A
David L. VandenBerg, M.D. VandenBerg, David L
W. Philip Bergman, M.D. Bergman, W. Philip
Donald A. Vance, M.D. Vance, Donald A
Mary M. Loehr, M.D. Loehr, Mary M
Kirk J. Raeber, D.O. Raeber, Kirk J
Michael L. Girard, M.D. Girard, Michael L
Dwight A. Lemon, M.D. Lemon, Dwight A
Richard J. Pfeiffer, M.D. Pfeiffer, Richard J
Mark Handy, M.D. Handy, Mark
Steven M. Temerlin, M.D. Temerlin, Steven M
Juan M. Tovar, M.D. Tovar, Juan M
Victor A. Maalouf, D.O. Maalouf, Victor A
Peter B. Mishky, M.D. Mishky, Peter B
Monique H. DeVoe, M.D. DeVoe, Monique H
Beverly Harrell-Bruder, M.D. Harrell-Bruder, Beverly
Agnes M. Stacia, M.D. Stacia, Agnes M
Christy R. Mohler, M.D. Mohler, Christy R
Peter F. Jost, M.D. Jost, Peter F
John A. Siefert, M.D. Siefert, John A