First empty cell

MartinDurham

Board Regular
Joined
Feb 12, 2016
Messages
70
Hi Folks


I have the following working VBS

Code:
Sub SaveVehicleChanges2()
    LastRow = Sheets("»MaintenanceSchedule").Cells(Rows.Count, "C").End(xlUp).Row + 1
        If Application.WorksheetFunction.CountIf(Sheets("»MaintenanceSchedule").Range("C1:C" & LastRow), ActiveSheet.Range("D4").Value) = 0 Then
            ActiveSheet.Range("D4").Copy Destination:=Sheets("»MaintenanceSchedule").Range("C" & LastRow)
                LastRow = LastRow + 1
                       MsgBox "Vehicle Added"
        Else
            MsgBox "Changes Saved"
        End If
End Sub

It copies the data in D4 on the current sheet and writes it to 1st empty cell in column C of the specified sheet (or so I thought)

I have since modified the "»MaintenanceSchedule" (target) sheet so that the 1st of every 5 cells in row c is blank like so:


9
10 =IF(ISBLANK(C9),"",(C9))
11 =IF(ISBLANK(C9),"",(C9))
12 =IF(ISBLANK(C9),"",(C9))
13 =IF(ISBLANK(C9),"",(C9))

It now writes the value to the very end of column C instead of the 1st empty one (in this case it should be C9), so it would seem I misunderstand what this VBS is actually doing.

any help is greatly appreciated



Martin
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Code:
    LastRow = Sheets("»MaintenanceSchedule").Cells(Rows.Count, "C").End(xlUp).Row + 1

This method to find the last row doesn't consider a null string returned by the formulas as empty.


Use this method instead.
Code:
LastRow = Sheets("»MaintenanceSchedule").Columns("C").Find("*", , xlValues, , 1, 2).Row + 1
 
Last edited:

MartinDurham

Board Regular
Joined
Feb 12, 2016
Messages
70
Thanks for your reply, replacing that line still causes the entry to be added down at the bottom of the column and not the 1st blank row.

Martin
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Thanks for your reply, replacing that line still causes the entry to be added down at the bottom of the column and not the 1st blank row.

Martin

I misunderstood. Try this.
Code:
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    LastRow = Sheets("»MaintenanceSchedule").Columns("C").SpecialCells(xlCellTypeBlanks)(1).Row
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]If[/color] LastRow = 0 [color=darkblue]Then[/color] LastRow = Sheets("»MaintenanceSchedule").Cells(Rows.Count, "C").End(xlUp).Row + 1
 

MartinDurham

Board Regular
Joined
Feb 12, 2016
Messages
70
Sorry for my tardy reply, I got side tracked with other projects.

This is working perfectly now. Many Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,803
Messages
5,598,131
Members
414,214
Latest member
marketingnumbersguy

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
Top