How to combine 2 codes?(limiting rows in column to X value)

[masofras]

Dear Excel Experts

Trying to solve problem with macro. However, my VBA coding skills are basic. Any help or hint is welcome

Here is code written correctly(code is not written by me)

Private Sub Worksheet_Calculate()'Check if values haven't changed
    If Range("F13").Value = Cells(Rows.Count, 1).End(xlUp).Value And _
       Range("K13").Value = Cells(Rows.Count, 2).End(xlUp).Value Then Exit Sub
       
'Store the new values
    Application.EnableEvents = False
    
    If Range("F13").Value <> Cells(Rows.Count, 1).End(xlUp).Value And _
       Range("F13").Value > 0 Then
        Cells(Rows.Count, 1).End(xlUp)(2).Value = Range("F13").Value
    End If
    
    If Range("K13").Value <> Cells(Rows.Count, 2).End(xlUp).Value And _
       Range("K13").Value > 0 Then
        Cells(Rows.Count, 2).End(xlUp)(2).Value = Range("K13").Value
    End If
    
    Application.EnableEvents = True
    
End Sub

What I need exactly? Lets say we limit/cant exceed rows in column to 48 (make range from A2 to A49) and IF any value reaches A49 then A2 delete any value, A49 is filled with new value and whole A2:A48 row moves up. It is cycle.

Something like code below :

Dim NR As Long    NR = Range("A" & Cells(Rows.Count).Row).End(xlUp).Row + 1
   Range("A" & NR).Value = Range("F13").Value
   If NR > 48 Then
    Range("A2:A48").Value = Range("A3:A49").Value
    Range("A49").ClearContents
    End If

I do not know how to combine those two codes. If you have any question just ask
Thanks for your willingness to help others

Have a nice day

 

[Badeea]

Hi masofras,

Try to copy and paste the following modified code. I have just created a "Combining" procedure to call it from "Worksheet_Calculate". Let me know if this is what you want.

Private Sub Worksheet_Calculate() 'Check if values haven't changed
If Range("F13").Value = Cells(Rows.Count, 1).End(xlUp).Value And _
Range("K13").Value = Cells(Rows.Count, 2).End(xlUp).Value Then Exit Sub

'Store the new values
Application.EnableEvents = False

If Range("F13").Value <> Cells(Rows.Count, 1).End(xlUp).Value And _
Range("F13").Value > 0 Then
Combining
End If

If Range("K13").Value <> Cells(Rows.Count, 2).End(xlUp).Value And _
Range("K13").Value > 0 Then
Combining
End If

Application.EnableEvents = True

End Sub


Sub Combining()
Dim NR As Long
NR = Range("A" & Cells(Rows.Count).Row).End(xlUp).Row + 1
Range("A" & NR).Value = Range("F13").Value
If NR > 48 Then
Range("A2:A48").Value = Range("A3:A49").Value
Range("A49").ClearContents
End If
End Sub

 

[masofras]

Hello badeea

At first thank you for reply and your spent time, much appreciated. It is working like a charm but one problem shows.
If I set higher NR value than 64(I mean 48 in code) in "Combining()" macro, it doesnt continue. It is stucked. If I set value lower NR value than 64 it is working perfect.

I found on other website solution. Code below:

[COLOR=#333333]Range("A" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("F13").Value[/COLOR]

Sorry, I didnt know that problem will show. If you can combine those 3 codes somehow, it will be perfect and final code.


Have a nice day

 

[Badeea]

Hi,

The following "Combining()" code should make it. Try and let me know what you get.

Sub Combining()
Dim NR As Long
NR = Range("A" & Cells(Rows.Count).Row).End(xlDown).Row + 1
Range("A" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("F13").Value
If NR > NR - 1 Then
Range("A2:A" & NR - 1).Value = Range("A3:A" & NR).Value
Range("A" & NR).ClearContents
End If
End Sub

 

[masofras]

Code showing me error:
Run-time error 1004
Method Range of object_Worksheet failed

Here is how my code looks like (NR = 70)

[COLOR=#333333]Sub Combining()[/COLOR]
[COLOR=#333333]Dim NR As Long[/COLOR]
[COLOR=#333333]NR = Range("A" & Cells(Rows.Count).Row).End(xlDown).Row + 1[/COLOR]
[COLOR=#333333]Range("A" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0).Value = Range("F13").Value[/COLOR]
[COLOR=#333333]If NR > NR - 1 Then[/COLOR]
[COLOR=#ff8c00]Range("A2:A[B]70[/B]" & NR - 1).Value = Range("A3:A[B]71[/B]" & NR).Value[/COLOR]
[COLOR=#333333]Range("A[B]71[/B]" & NR).ClearContents[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

If I click debug, excel shows me that error is in dark orange row.

Thanks