Counting number of non blank cells and inserting that amount on another sheet

[cheeze83]

Hi All

I've been working on a bit of code to do as the title suggests, have tried a variety of things, but they all seem to be having the same affect.

The basic overview is that throughout the month, a colleague will be inputting people to this system, and at the end of the month the data will be sent to our accounts department.

The accounts team need a very specific form, which is fine and is set up - but by default I only want it to have a couple of lines where the employee details are entered, then when the macro is ran, I want it to count how many entries have been input throughout the month from the data capture sheet, and insert this amount of rows into the accounts sheet.

I so far have the below code:

Sheets("Data Capture").Select
    nrows = Application.WorksheetFunction.CountA(Worksheets("Data Capture").Range("A:A"))
    Sheets("Account Form").Select
    Sheets("Account Form").Rows("21").Copy
    Sheets("Account Form").Rows("22:" & nrows).Select
    ActiveSheet.Paste
    Sheets("Data Capture").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Account Form").Select
    Range("B20").Select

The code is unfinished at the end, as you may be able to see once inputting the rows, it goes back and copies the field, then inputs - this bit is fine, I just haven't got past this obstacle yet.

The problem is, whilst the above affectively does what I want it to, it's inputting the wrong amount of lines. I have added and removed data from the Data Capture form to test, and regardless of there being 3 or 9 cells in "A" with data in, it inputs 19 rows each time!

Anyone able to help with this at all? I have checked the cells and there is no hidden formula's/text or anything in them.

[ragulduy]

Maybe you could try something along the lines of:

Sub Macro_1()
Dim rng, non_bl
Set rng = Range("A1:D7")
non_bl = rng.Cells.Count - rng.SpecialCells(xlCellTypeBlanks).Cells.Count
End Sub

?

[cheeze83]

Hi Yudlugar

That seems to work, but now encountering a bit of a problem with the formatting of the entered cells.

Below is before and after running macro

Before

before.png

After

after.png

Is there something I should possibly use other than

Sheets("Account Form").Rows("21").Copy
    Sheets("Account Form").Rows("22:" & non_bl).Select
    ActiveSheet.Paste

Thank you though for previous response - has got me a bit closer to the end of the tunnel =)

[ragulduy]

Please can you upload your workbook with the full code, I can't tell at the moment.

[cheeze83]

Hi

Please see attached - thank you =)

[ragulduy]

I'm not 100% sure it's what you want it to do but I think what you are looking for is:

Dim non_bl
    non_bl = Sheets("Prepare PARF").Range("A" & Rows.Count).End(xlUp).Row - 2
    Sheets("PARF Form").Select
    Sheets("PARF Form").Rows("21:" & 21 + non_bl).Insert shift:=xlDown

[cheeze83]

PERFECT. Thank you very much! The only issue I have now is that where I have 3 rows already there, it has 3 too many at the end, however as this is always going to be the case, changing the -2 at the end to -5 should fix this issue.

Thanks again!

[ragulduy]

I'd try something like:

    Dim non_bl
    non_bl = Sheets("Prepare PARF").Range("A" & Rows.Count).End(xlUp).Row - 2
    if non_bl<3 and non_bl > 0 then
       Sheets("PARF Form").Rows("21:" & 21 + 2 -non_bl).delete shift:=xlup
    elseif non_bl>3 then
       Sheets("PARF Form").Rows("21:" & 21 + non_bl-3).insert shift:=xlDown
    end if

[cheeze83]

That's spot on - I did notice my -5 didn't work, it just messed up the formatting - your way does it great!