Wish I could solve this Clear Range Problem

M

microhunt

Board Regular
Joined
Aug 14, 2017
Messages
63
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:
 
Sort by date Sort by votes
Again Dim I as long (correct for 32bit) but with the last code you posted it is for both 32bit and 64bit Excel and so I am not going to explain it when only on my phone as you are obviously not aware of what your code currently does.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi Mark,

Thanks for taking the time to help, I was told earlier to you Dim Long and it would work but I can not get code to work with Dim. Maybe someone else will look later and help me to keep Option Explicit and use Dim.

Thanks
 
Upvote 0
I am aware that my code was updated in July 2012 to run with Windows 64bit. I update the code at this time see below:

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
 
Upvote 0
Can get code to work using (Dim I as Long) or (Dim I as Integer)

This code looks the best, but it is also clear other cells in column C


Code:
Code: 
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

Can anyone see what is wrong with this code?

Also if I used this line of code I can use Option Explicit
 
Last edited:
Upvote 0
No point on modern computers using Integer. Long will work on 32bit computers.
 
Upvote 0
MARK858 said:
No point on modern computers using Integer. Long will work on 32bit computers.
Click to expand...

Look is there anyone out there tonight to help me get this code to work without revoking Option Explicit. So just to Summarise my problem. Here is code below as follows:

Rich (BB code): 
Option Explicit


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


#If  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


#Else 
    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
#End  If


Private Const EM_SETPASSWORDCHAR = &HCC


#If  VBA7 Then
    Private Const nIDE                  As LongPtr = &H100
    Private hdlEditBox                  As LongPtr
    Private Fgrndhdl                    As LongPtr
#Else 
    Private Const nIDE                  As Long = &H100
    Private hdlEditBox                  As Long
    Private Fgrndhdl                    As Long
#End  If
#If  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
#Else 
    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
#End  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 ("Q4:Q999"), 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

Code works OK if I don't use Option Explicit (commenting Out) The extra piece of code I have put in is highlighted in red. I need to use the Dim Long command, I have tried several modifications none of which work.

I want to keep Option Explicit, surely there must be a Genius somewhere tonight who can suggest modification of formula in red??
 
Upvote 0
Looks like when I get a brain, I will Learn

Works Now, Thanks Everybody but especially myself!!!

Works Perfect!!
Thanks


Code: 
[/COLOR]
[COLOR=#333333]Dim i as long[/COLOR]
[COLOR=#333333]For i = 5 To 997 Step 4[/COLOR]
[COLOR=#333333]Cells(i, 3).ClearContents[/COLOR]
[COLOR=#333333]Next i[/COLOR]
[COLOR=#333333]]/CODE]

Bakerman2 good friend

Cannot believe medicine had me spelling Dimi and not Dim i for the last several hours.



[/COLOR]
 
Upvote 0
Crossposted https://www.excelforum.com/excel-programming-vba-macros/1218248-macro-to-clear-data-on-column.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hmmm, another OP for me to ignore in future.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Problem with VBA Code in Libre
Replies
0
Views
648
microhunt
M
M
  • Question Question
Clearing selected Range
Replies
6
Views
616
microhunt
M
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
83
Fluff
Fluff
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
237
Thlama
T
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
410
NoSparks
N

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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