Wish I could solve this Clear Range Problem

M

microhunt

Board Regular
Joined
Aug 14, 2017
Messages
60
Office Version
  1. 2021
Platform
  1. Windows
I am using the code below to clear cells when I run Macro. The problem my sheet is very long for example. I am clearing every fourth cell in column C starting at C5 and finishing at C1653. I do not want to clear any other cells in this column.

I posted this yesterday and a very kind person nearly give me a fix using the code below which did work but also clear other formulas in column C. The code he give me was as follows:

Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearFishSheet(sWorksheet As String)
  Sheets(sWorksheet).Range("C5:C1653") = Evaluate("IF(MOD(ROW('" & Sheets(sWorksheet).Name & "'!C5:C1653)-5,4),'" & Sheets(sWorksheet).Name & "'!C5:C1653,"""")")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]

Sub Clearrange(sRange As String, sWorksheet As String)
Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub
Sub ClearFishSheet(sWorksheet As String)



Code: 
[COLOR=#333333]Clearrange ("C5"), sWorksheet ' Range 1[/COLOR]
[COLOR=#333333]Clearrange ("C9"), sWorksheet ' Range 2[/COLOR]
[COLOR=#333333]Clearrange ("C13"), sWorksheet ' Range 3[/COLOR]
[COLOR=#333333]Clearrange ("C17"), sWorksheet ' Range 4[/COLOR]
[COLOR=#333333]Clearrange ("C21"), sWorksheet ' Range 5[/COLOR]
[COLOR=#333333]Clearrange ("C25"), sWorksheet ' Range 6[/COLOR]
[COLOR=#333333]Clearrange ("C29"), sWorksheet ' Range 7[/COLOR]
[COLOR=#333333]Clearrange ("C33"), sWorksheet ' Range 8[/COLOR]
[COLOR=#333333]Clearrange ("C37"), sWorksheet ' Range 9[/COLOR]
[COLOR=#333333]Clearrange ("C41"), sWorksheet ' Range 10[/COLOR]
[COLOR=#333333]Clearrange ("C45"), sWorksheet ' Range 11[/COLOR]
[COLOR=#333333]Clearrange ("C49"), sWorksheet ' Range 12[/COLOR]
[COLOR=#333333]Clearrange ("C53"), sWorksheet ' Range 13[/COLOR]
[COLOR=#333333]Clearrange ("C57"), sWorksheet ' Range 14[/COLOR]
[COLOR=#333333]Clearrange ("C61"), sWorksheet ' Range 15[/COLOR]
[COLOR=#333333]Clearrange ("C65"), sWorksheet ' Range 16[/COLOR]
[COLOR=#333333]Clearrange ("C69"), sWorksheet ' Range 17[/COLOR]
[COLOR=#333333]Clearrange ("C73"), sWorksheet ' Range 18[/COLOR]
[COLOR=#333333]Clearrange ("C77"), sWorksheet ' Range 19[/COLOR]
[COLOR=#333333]Clearrange ("C81"), sWorksheet ' Range 20[/COLOR]
[COLOR=#333333]Clearrange ("C85"), sWorksheet ' Range 21[/COLOR]
[COLOR=#333333]Clearrange ("C89"), sWorksheet ' Range 22[/COLOR]
[COLOR=#333333]Clearrange ("C93"), sWorksheet ' Range 23[/COLOR]
[COLOR=#333333]Clearrange ("C97"), sWorksheet ' Range 24[/COLOR]
[COLOR=#333333]Clearrange ("C101"), sWorksheet ' Range 25
[/COLOR]
[/QUOTE]

I would really appreciate help with this. I apologise in advance if I have broken any forum rules by posting

:confused:
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Code: 
For i = 5 To 1653 Step 4
    Cells(i, 3).ClearContents
Next i

you can use a loop... it is kinda hard to read your post cause your quote tag is using two closing tags... that code will go down column C and clear every 4th cell starting at row 5 and end when it gets to 1653
 
Upvote 0
Thanks for your help, I am going to test your code now, will let you know in 2 or 3 minutes:)
 
Upvote 0
I ran your code in my macro as follows:

Code: 
Sub Clearrange(sRange As String, sWorksheet As String)
    Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub
Sub ClearFishSheet(sWorksheet As String)


For i = 5 To 997 Step 4
    Cells(i, 3).ClearContents
Next i


    Clearrange ("A4:A7"), sWorksheet     ' Range 18
    Clearrange ("E4:E7"), sWorksheet     ' Range 20
    Clearrange ("K4:L999"), sWorksheet    ' Range 21
    Clearrange ("P4:P999"), sWorksheet    ' Range 22
    Clearrange ("F4:F7"), sWorksheet     ' Range 23
    Clearrange ("C9"), sWorksheet        ' Range 24
    Clearrange ("E8:E999"), sWorksheet    ' Range 25
    Clearrange ("A4:B999"), sWorksheet    ' Range 26


End Sub

When I run the code I get an error which says compile error variable not defined?

cerfani said:
Code: 
For i = 5 To 1653 Step 4
    Cells(i, 3).ClearContents
Next i

you can use a loop... it is kinda hard to read your post cause your quote tag is using two closing tags... that code will go down column C and clear every 4th cell starting at row 5 and end when it gets to 1653
Click to expand...
 
Upvote 0
Dim i as long or comment out your Option Explicit.
 
Last edited:
Upvote 0
Can you please help me with this as I don't grasp what you mean, here is my complete code:

Code: 
Option Explicit


''/////////////////////////////////////////////////////////////////
''//  14th August 2017 Revised For New Code //
''// Amended
''/////////////////////////////////////////////////////////////////


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Public Declare PtrSafe Function GetActiveWindow Lib "user32" () As Long


    Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
                                                                                    ByVal hWnd1 As LongPtr, _
                                                                                    ByVal hWnd2 As LongPtr, _
                                                                                    ByVal lpsz1 As String, _
                                                                                    ByVal lpsz2 As String) _
                                                                                    As LongPtr


    Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, _
                                                                                   ByVal wMsg As Long, _
                                                                                   ByVal wParam As LongPtr, _
                                                                                   lParam As Any) _
                                                                                   As LongPtr


    Public Declare PtrSafe Function SetTimer Lib "user32" ( _
                                                            ByVal hwnd As LongPtr, _
                                                            ByVal nIDEvent As LongPtr, _
                                                            ByVal uElapse As Long, _
                                                            ByVal lpTimerFunc As LongPtr) _
                                                            As LongPtr


    Public Declare PtrSafe Function KillTimer Lib "user32" ( _
                                                            ByVal hwnd As LongPtr, _
                                                            ByVal nIDEvent As LongPtr) _
                                                            As Long


    Public Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Public Declare Function GetActiveWindow Lib "user32" () As Long


    Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
                                                                            ByVal hWnd1 As Long, _
                                                                            ByVal hWnd2 As Long, _
                                                                            ByVal lpsz1 As String, _
                                                                            ByVal lpsz2 As String) _
                                                                            As Long


    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
                                                                           ByVal wMsg As Long, _
                                                                           ByVal wParam As Long, _
                                                                           lParam As Any) _
                                                                           As Long


    Public Declare Function SetTimer Lib "user32" ( _
                                                    ByVal hwnd As Long, _
                                                    ByVal nIDEvent As Long, _
                                                    ByVal uElapse As Long, _
                                                    ByVal lpTimerFunc As Long) _
                                                    As Long


    Public Declare Function KillTimer Lib "user32" ( _
                                                    ByVal hwnd As Long, _
                                                    ByVal nIDEvent As Long) _
                                                    As Long


    Public Declare Function GetForegroundWindow Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Const EM_SETPASSWORDCHAR = &HCC


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Const nIDE                  As LongPtr = &H100
    Private hdlEditBox                  As LongPtr
    Private Fgrndhdl                    As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Const nIDE                  As Long = &H100
    Private hdlEditBox                  As Long
    Private Fgrndhdl                    As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Public Function TimerFunc( _
        ByVal hwnd As LongPtr, _
        ByVal wMsg As Long, _
        ByVal nEvent As LongPtr, _
        ByVal nSecs As Long) As Long
    Dim hdlwndAct As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Public Function TimerFunc( _
        ByVal hwnd As Long, _
        ByVal wMsg As Long, _
        ByVal nEvent As Long, _
        ByVal nSecs As Long) As Long
      
        Dim hdlwndAct As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    '// Do we have a handle to the EditBox
    If hdlEditBox > 0 Then Exit Function
  
    '// Get the handle to the ActiveWindow
    hdlwndAct = GetActiveWindow()
  
    '// Get the Editbox handle
    hdlEditBox = FindWindowEx(hdlwndAct, 0, "Edit", "")
  
    '// Set the password character for the InputBox
    SendMessage hdlEditBox, EM_SETPASSWORDCHAR, Asc("*"), ByVal 0


End Function


Public Function InPutBoxPwd(fPrompt As String, _
    Optional fTitle As String, _
    Optional fDefault As String, _
    Optional fXpos As Long, _
    Optional fYpos As Long, _
    Optional fHelpfile As String, _
    Optional fContext As Long) As String
    
    Dim sInput As String
    
    '// Initialize
    hdlEditBox = 0
    Fgrndhdl = GetForegroundWindow
    '// Windows-Timer
    SetTimer Fgrndhdl, nIDE, 100, AddressOf TimerFunc
    
    '// Main InputBox
    If fXpos Then
        sInput = InputBox(fPrompt, fTitle, fDefault, fXpos, fYpos, fHelpfile, fContext)
    Else
        sInput = InputBox(fPrompt, fTitle, fDefault, , , fHelpfile, fContext)
    End If
    
    '//
    KillTimer Fgrndhdl, nIDE
    '// Pass result
    InPutBoxPwd = sInput
    


End Function


Sub Clearrange(sRange As String, sWorksheet As String)
    Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub
Sub ClearFishSheet(sWorksheet As String)


For i = 5 To 997 Step 4
    Cells(i, 3).ClearContents
Next i


    Clearrange ("A4:A7"), sWorksheet     ' Range 18
    Clearrange ("E4:E7"), sWorksheet     ' Range 20
    Clearrange ("K4:L999"), sWorksheet    ' Range 21
    Clearrange ("P4:P999"), sWorksheet    ' Range 22
    Clearrange ("F4:F7"), sWorksheet     ' Range 23
    Clearrange ("C9"), sWorksheet        ' Range 24
    Clearrange ("E8:E999"), sWorksheet    ' Range 25
    Clearrange ("A4:B999"), sWorksheet    ' Range 26


End Sub


Sub ClearFish()
Const ok As String = "fish"
Dim pw As String
pw = InPutBoxPwd("Enter password") '<<<<<<<<<<<<<<<<<<<<<< Changed line
If pw <> ok Then
    MsgBox "Wrong password"
    Exit Sub
End If


Call ClearFishSheet("Fish")


End Sub
 
Upvote 0
Put an apostrophe in front of Option Explicit.
 
Upvote 0
Happy it helped even though it is really not the correct answer to give but saves me explaining how to do it correctly on my phone.
 
Upvote 0
I would like to you option explicit as all my variables was declared and the code worked until I inserted this code

For i = 5 To 1653 Step 4
Cells(i, 3).ClearContents
Next i
is a way to keep Option Explicit and get this code to work?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Problem with VBA Code in Libre
Replies
0
Views
534
microhunt
M
M
  • Question
Clearing selected Range
Replies
6
Views
545
microhunt
M
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
260
NoSparks
N
D
  • Question
equation for forward and back
Replies
2
Views
277
dmheller
D
P
  • Solved
Computing investment return formula
Replies
8
Views
477
polarwoc
P

Forum statistics

Threads
1,215,963
Messages
6,127,964
Members
449,414
Latest member
sameri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top