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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top