Posts by Alterego

Quote

Originally posted by Richie(UK)
Hi,

How about this:

Tools menu / Options / View tab and Window options - untick zero values.

To post an attachment use the Browse button at the side of the Attachment box and navigate to the file concerned. The box is the last item you see in the posting area (just below the Helpful Info ... section).

HTH

Display More

THank You,

This did help my Cause. I attached the spreadsheet in case anyone wanted to look at it.

I have asked this before, but I added it to an old topic so it's probably not getting looked at. I have code to read several timesheets in a certain dir and compile another spreadsheet from the data in the spreadsheets. The problem I have is when the macro looks up the cell range, and there is no data to report, it puts 0 values in the cells. Is there a way to just skip the process if there is no data to report? In otherwards, leaving the 0's out of the spreadsheet. I'll past some snipits here.

Part 1:

For x = A To Z
Hours_REG = Application.WorksheetFunction.Sum(sht.Range("D" + Format(x)), _
sht.Range("H" + Format(x)), _
sht.Range("L" + Format(x)), _
sht.Range("P" + Format(x)), _
sht.Range("T" + Format(x)), _
sht.Range("X" + Format(x)), _
sht.Range("AB" + Format(x)))

OTHOURS_X1 = Application.WorksheetFunction.Sum(sht.Range("E" + Format(x)), _
sht.Range("I" + Format(x)), _
sht.Range("M" + Format(x)), _
sht.Range("Q" + Format(x)), _
sht.Range("U" + Format(x)), _
sht.Range("Y" + Format(x)), _
sht.Range("AC" + Format(x)))

OTHOURS_X2 = Application.WorksheetFunction.Sum(sht.Range("F" + Format(x)), _
sht.Range("J" + Format(x)), _
sht.Range("N" + Format(x)), _
sht.Range("R" + Format(x)), _
sht.Range("V" + Format(x)), _
sht.Range("Z" + Format(x)), _
sht.Range("AD" + Format(x)))

OTHOURS_X3 = Application.WorksheetFunction.Sum(sht.Range("G" + Format(x)), _
sht.Range("K" + Format(x)), _
sht.Range("O" + Format(x)), _
sht.Range("S" + Format(x)), _
sht.Range("W" + Format(x)), _
sht.Range("AA" + Format(x)), _
sht.Range("AE" + Format(x)))

Part 2:

If Hours > 0 Then
row = ListPointer
Sheet3.Cells(row, 1) = eName
Sheet3.Cells(row, 2) = eNumber
Sheet3.Cells(row, 3) = eCentre
Sheet3.Cells(row, 4) = Week
Sheet3.Cells(row, 5) = Activity
Sheet3.Cells(row, 6) = Hours_REG
Sheet3.Cells(row, 7) = FileName
Sheet3.Cells(row, = OTHOURS_X1
Sheet3.Cells(row, 9) = OTHOURS_X2
Sheet3.Cells(row, 10) = OTHOURS_X3
ListPointer = ListPointer + 1
End If
Next x
End If

Is there a way to edit this code to not show 0 values?

BTW, how do I attach an excel file to this post?

I think I may be able to help you out with this one. Here is something similar that I use.

If IsText(TextBox2.Text) = False Then
MsgBox "Invalid Data"
Exit Sub
End If

Is there a way I can edit this code to leave 0 values as blank cells?

I was just thinking about why the 0's are there in the 1st place. Maybe we can prevent the fire before it happens. Here's the code that refers to that area of the spreadsheet. Maybe there's a way to stop the 0's from happeneing.

Here is the 1st Part:

Hours_REG = Application.WorksheetFunction.Sum(sht.Range("D" + Format(x)), _
sht.Range("H" + Format(x)), _
sht.Range("L" + Format(x)), _
sht.Range("P" + Format(x)), _
sht.Range("T" + Format(x)), _
sht.Range("X" + Format(x)), _
sht.Range("AB" + Format(x)))

OTHOURS_X1 = Application.WorksheetFunction.Sum(sht.Range("E" + Format(x)), _
sht.Range("I" + Format(x)), _
sht.Range("M" + Format(x)), _
sht.Range("Q" + Format(x)), _
sht.Range("U" + Format(x)), _
sht.Range("Y" + Format(x)), _
sht.Range("AC" + Format(x)))

OTHOURS_X2 = Application.WorksheetFunction.Sum(sht.Range("F" + Format(x)), _
sht.Range("J" + Format(x)), _
sht.Range("N" + Format(x)), _
sht.Range("R" + Format(x)), _
sht.Range("V" + Format(x)), _
sht.Range("Z" + Format(x)), _
sht.Range("AD" + Format(x)))

OTHOURS_X3 = Application.WorksheetFunction.Sum(sht.Range("G" + Format(x)), _
sht.Range("K" + Format(x)), _
sht.Range("O" + Format(x)), _
sht.Range("S" + Format(x)), _
sht.Range("W" + Format(x)), _
sht.Range("AA" + Format(x)), _
sht.Range("AE" + Format(x)))

Here is the 2nd part:

If Hours > 0 Then
row = ListPointer
Sheet3.Cells(row, 1) = eName
Sheet3.Cells(row, 2) = eNumber
Sheet3.Cells(row, 3) = eCentre
Sheet3.Cells(row, 4) = Week
Sheet3.Cells(row, 5) = Activity
Sheet3.Cells(row, 6) = Hours_REG
Sheet3.Cells(row, 7) = FileName
Sheet3.Cells(row, = OTHOURS_X1
Sheet3.Cells(row, 9) = OTHOURS_X2
Sheet3.Cells(row, 10) = OTHOURS_X3
ListPointer = ListPointer + 1
End If

Maybe the problem can be solved within this code. I appreciate the help. You guys have helped me along with my projects, and beleive me, I appreciate it.

:cheers:

Looks like everything works ok.

Hi all,

I have written a macro that will go to a directory and read all of the spreadsheets in that directory. The macro will then generate a report from the data taken from about 400 spreadsheets in the directory.

Now for the question:
I want to clear the contents of all cells with a "0" value. I don't know how long the spreadsheet will be so I can't put a specific cell range. I was wondering the correct syntax for infinite cell range between 3 colums. Would it be IE:
If cells.range("H2:J*") = 0 then
cells.clear
end if

Does this seem right? Any help would be great.

/Dave

Thank You very much.

/Dave

:cheers:

Hi,

I have programmed one of my spreadsheets to Autosend in Outlook to a specific Address when I close the spreadsheet. Now what I would also like is to auto save as well. I can add code to save to a specific directory, but I would like the browse window to open so I could save to a certain dir. Kind of like using the saveas option in the file menu. I know it's easy to just go File/Save As, but this is a spreadsheet that I have designed for multiple users, (65 in Total) and to avoid any screw ups, I would like to add the autosave when closing the timesheet. Can anyone tell me how to get the save as browse window to open?

Thank You

Quote

Originally posted by Rennie
Another way of doing this not using macros is as follows. In the adjasent column to your data type in an if formula -if(cell=0,"T",1) and copy it down to your data range. Then select the formula range and edit>goto>special>text. This will select all the rows that have 0 values. Now edit>delete>row.

Hope this helps.
Rennie

Thank You very Much. This solved my problem.

:cheers:

The macro ran, but nothing happened. What I have is 2 Colums A and B. Column A has the material names and Column B has the dollar value. If the dollar value = 0, then I want to delete that row. I could manually do this, but as you can see, there's 3500 lines to go through. To do this for 10 accounts will be a huge pain in the rear end. If you have any other ideas, I'm open to suggestions.

:cheers:

Hi, I was trying to write a macro for a spreadsheet I have. What I would like to do is, between a range of cells, if the cell value is 0 then delete the entire row. Now I have been working on code for this, but I have failed every time with the macro deleting all rows. Here are some snipits from my code.

Sub delete()
For Each C In Worksheets("Sheet2").Range("B4:B3556")
If C.Value = 0 Then
Rows.delete
End If
Next C
End Sub

Any help with this would be Great.

:guitar: