How to simplify this macro

I am extremely sorry for that sir. Since I am in front of the system and I will get the system only for a short time, thus only i mentioned so. I will do the editing.

Originally Posted by millz

Hi aneshdas, first, it's really not very nice to say things like "PLEASE HELP ASAP", it sounds even worse when in caps. People here give help out of good will, not obliged to. Next, it would help if you gave same info on what your macro does, you are familiar with what you are doing, but others are not.

And how much time do you mean when you say "taking much time to process"? It seems you are only looping through about 30 columns of 500 rows, should not be taking too long.

See if this makes any difference?

Private Sub Button506_Click()

    Dim BeginCol As Long
    Dim endCol As Long
    Dim ChkRow As Long
    Dim Colcnt
    Dim rng As Range
    Dim c As Variant

    Application.ScreenUpdating = False
    BeginCol = 6
    endCol = 37
    ChkRow = 7

    With Sheets("Sheet1")
        For Colcnt = BeginCol To endCol
            'Sheets("Sheet1").Cells(ChkRow, Colcnt).Select ' <- I don't know why this was necessary
            If .Cells(ChkRow, Colcnt).Value = Date Then
                Set Rng = .Cells(ChkRow, Colcnt).Offset(1).Resize(500)
                For Each c In Rng
                    If .Cells(c.row, 2).Value <> "" Then
                        If c.Value = 0 Then
                            c.Value = "P"
                        End If
                    End If
                Next c
            Else
                'Sheets("Sheet1").Cells(ChkRow, Colcnt).EntireColumn.Hidden = True
            End If
        Next Colcnt
    End With
    Application.ScreenUpdating = True
    
End Sub

Sir, My requirement is mentioned below. Kindly help me sir. Also the code which I have posted taking 8 second to complete the process since rest of the macros takes only fraction of a second.

"I have a spreadsheet for entering Employee's details. On that I am entering employees daily attendance status. It is almost 600 employees and entering each and every employee is a herculean task. So What I need is, I can enter on the Absent, Casual Leave, and etc...and the remaining unmarked staffs will be PRESENT. So that If I have a command button for that, when I click that button it should automatically apply "P" on the remaining cells on that particular date's column. More clearly, I have 31 columns for each days in month and on each column's 7th ROW contains that particular day's date. So the macro has to search the empty CELL's between current date's particular column and fill it with "P". The empty cells will be between 8th row to 500th row on each day's column. One more thing the macro has to check. The empty cell on each day has to fill ONLY IF that cells respective "B" cell having any value. More clear I am entering Employees name in the "B" Column from 8 th to 500th row. So, After clicking the command button, macro has to find that particular date containing column and find the empty cells between that column's 8th ROW to 500th ROW and has to fill those empty CELLS with "P" ONLY IF there is any name in the B column."

Kindly Suggest me a macro for this.

For Each c In rng
                If Sheets("Sheet1").Cells(c.Row, 2).Value <> "" Then
                    If c.Value = 0 Then
                        c.Value = "P"
                    End If
                End If
            Next c

Only this area is taking much time when I checked. :-(

Yes Sir, that too is taking the same time. :-(

Private Sub Button506_Click()
Application.ScreenUpdating = False
BeginCol = 6
    endCol = 37
    ChkRow = 7
For Colcnt = BeginCol To endCol
        If Sheet1.Cells(ChkRow, Colcnt).Value = Date Then
    Sheet1.Cells(ChkRow, Colcnt).Rows("2:500").Select
Selection.Replace What:="", Replacement:="P", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
     Else
            Sheet1.Cells(ChkRow, Colcnt).EntireColumn.Hidden = True
   
   End If

Next Colcnt
Application.ScreenUpdating = True
End Sub

This was the code I used first. This was perfectly fast and takes only a fraction of second. But this Code is not checking whether there is any name in the B column or not.

So how to overcome that barrier sir?

Yes Sir, I am using Data Validation on the selected columns. But how come it working very fast when I used the last code which I mentioned last? Kindly provide me a solution for this sir.

Still the same speed. :-( Validation has restored.....

The thing I don't understood is why it is not taking any time on the code I have entered first, which is not checking the status of 2 column...

this is not working Since c.Value is not Zero. i replaced the Zero with "". but the result is the same.

Sir,

Forget about the code I provided. Can you please give me a new code based on my requirements mentioned above? Please

aneshdas,
as i read the posts, your solution works but slowly, yet it still works... so 100% for the original problem is solved

So it may be a bit slow, I suggest that instead of asking for "new code" to magically fix your "issue" that you actually experiment a bit.

You seem to have identified the area in your code that is slow, do some research on how to achieve the same result in a quicker time

And I have to repeat a mantra from many other posts... include a sample with your problem clearly shown as well as your expected result... a lot of us like to simply "play" until we get an answer but we "HATE" creating test data for a situation we don't understand.

we are a self help group, not a free complete solution provider.

play by the expected rules and you will be rewarded, make demands like "give me the new code" and you will quickly run out of help

Friends,

Kindly go through the attached sample file and please provide me a solution to speed up the process.

Sir,
Have you got the sample file? Hope you have seen the issue.

Sir,
Is it because the Columns except today date is already hidden. So is that why the process taking time since going through hidden column is expensive??