Macro to delete unnecessary rows or add additional rows

[Raidon]

Dear all,

I'm in the market for a string of VBA code that will either delete unnecessary rows or add additional rows depending on a changing variable selected at the beginning of a process. Some more details:

The changing variable I mentioned is a list of countries. When you select the country it generates a list of all sites within that country in Sheet 1. Sheet 2 contains a formula which replicates each site 10 times underneath one another.
Obviously the different countries will have a varying number of sites operating within them, and as a result the number of rows in Sheet 2 will vary depending on the country you select. I then have to manually delete the unnecessary rows or add additional rows in Sheet 2, depending on the country selected in Sheet 1 in order for the formulas to function properly.

To help this process I have a formula in Sheet 1 E16 which calculates what the very bottom line of my data table in Sheet 2 needs to be, and converts it into a cell reference. For example: I select the UK, the formula calculates that the bottom row of the data table should be row 100, converted to cell reference A100. I then use "Go To" (Ctrl + G) to quickly go to that bottom row in Sheet 2. I then delete all unnecessary rows beneath it or fill down (Ctrl + D) so that my data table range is from A12:X100.

I would like a macro to do this automatically. What is needs to is:

1) In Sheet 2, highlight A12:X12 (top row of my data table)
2) Fill down (Ctrl + D) all the way down to A5500:X5500
3) Go to the cell reference calculated in Sheet 1 E16 (A100 for example) and delete all lines below it (A101 and below, for example).

Any assistance will be greatly appreciated.

Many thanks

 

[onlyadrafter]

Hello,

think this will be a start for your request

Sub ADD_ROWS()
    MY_ROWS = Sheets("Sheet1").Range("E16").Value
    With Sheets("Sheet2")
        .Range("A12:X12").Copy .Range("A13:X" & MY_ROWS)
    End With
End Sub

 

[Raidon]

Hi onlyadrafter,

Thanks for the reply, but this does not work entirely. When running the code, the following debug message pops up:

"Object variable or With block variable not set".

Unfortunately I do not understand VBA language enough to know what it means.

I've done some work in the mean time and this is what my code should ideally do, apart from two parts:

Sub Update()


' Update Macro


'  Filling A12:X5500


    Sheets("Sheet2").Select
    Range("A12:X5500").Select
    Selection.FillDown
    
'  Identifying bottom row and deleting rows below it


    Sheets("Sheet1").Activate
    Range("E16").Select
    Selection.Copy
    Sheets("Sheet2").Activate
    Application.Goto Reference:="R151C1"
    ActiveCell.Offset(1, 0).Select
    Rows("152:152").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
End Sub

1) The "Goto reference" line should not return R151C1. Instead it should return the cell reference calculated in Sheet1 E16.
2) The "Rows" line should not return a hardcoded "152:152". It needs to be dynamic and highlight the row identified by the Offset formula.

Any help would be greatly appreciated.

Many thanks

 

[onlyadrafter]

Hello,

have amended your code, can you figure it out?

Sub Update()
' Update Macro
'  Filling A12:X5500
    Sheets("Sheet2").Select
    Range("A12:X5500").Select
    Selection.FillDown
'  Identifying bottom row and deleting rows below it
    Sheets("Sheet1").Activate
    Range("E16").Select
    MY_OFFSET = Selection
    Selection.Copy
    Sheets("Sheet2").Activate
    Application.Goto Reference:="R" & MY_OFFSET & "C1"
    ActiveCell.Offset(1, 0).Select
    Rows(ActiveCell.Row & ":" & Rows.Count).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
End Sub

The code I originally supplied works for me, which line is the error on?

 

[Raidon]

Hello,

This works perfectly! Thank you very much for your help. Really appreciated.

Regarding your original code, I believe it is because I chose to leave the "Option Explicit" on, which forces me to define my variables.
I defined MY_ROWS as a Range, and it didn't like it since that is what was driving the error.

This has been fixed by turning off that option. Lesson learnt.

Thank you again for your help!