• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Define number of rows to populate

Chris Turner

New Member
I am using a macro to allow data to be entered into a spreadsheet in Columns A and B down 30 rows. I am trying to find a way to define the number of cells based on specific needs. Some times I will need 20 rows of data, other times I will need 30 rows populating, etc. There is a delay in the macro to allow the data to be entered into the last cell before it clears the columns.

I am using this macro:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Count([A1:B30]) = 60 Then
Application.Wait (Now + TimeValue("0:00:03"))
    [A1:B30].ClearContents
    Range("A1").Select
End If
End Sub

I want to be able to vary the number of rows. I thought of having the macro read from a hidden cell in the worksheet but not sure if this is the best way to do this. Appreciate any suggestions or help.
 

Attachments

  • Delay_Macro_and_Clear_Contents.xlsm
    18.2 KB · Views: 2
Last edited by a moderator:
What is going to define the Number of Rows?
Why not have a button to execute the macro?
 
I did originally have a button to activate the macro but this required someone to press the button. The worksheet is used for quality inspection to measure the size of a part. It requires a specific number of units to be measured based on the sample size for each production quantity. This is why the number of rows of data will vary per the sample size. It could be 20, and the next part may require 30 parts and so on.
 
You need to define the logic that triggers the event ?
Excel doesn’t understand next time maybe 30?
 
Thank you for your feedback. I was hoping to basically keep the same macro but instead of defining the range as highlighted in red

PrivateSub Worksheet_Change(ByVal Target As Range)
If Application.Count([A1:B30]) = 60 Then
Application.Wait (Now + TimeValue("0:00:03"))
[A1:B30].ClearContents
Range("A1").Select
EndIf
EndSub

Have the macro control the count for the number of rows to populate and clear contents based on a cell value entered in the worksheet. This is what I was hoping to achieve. Is this possible?
 
It certainly is.
Basically you need to change how you reference range. Instead of [Range], you'll need to use Range object.

Assuming Cell D1 contains Row#
Code:
If Application.Count(Range("A1:B" & [D1].Value)) = ([D1].Value * 2) Then
    'Rest of code and change subsequent range reference like above....
 
Hi Chihiro, thanks for the reply. I appreciate your help.

What would I do with the clear content range? How would the macro be able to read Cell D1 value to clear the content?
 
The macro works perfectly for the sampling and inspection operation. I truly appreciate everyone's help and support.
 
Back
Top