VBA: Code to find first empty cell in column A

[Puppies72]

Hi all,

I'm struggling my way through a macro with help from you all and am bringing in data from several sheets into a single sheet - what I need to do is to copy certain ranges (which I know how to select and do now) but then I need to paste it beneath the last active cell in column A which can vary.

So for instance if the first part of my macro copies in 450 entries into column A and the next part selects 100 entries it needs to paste those starting from A451 (the next empty cell).

The number of entries copied in will vary every time which is why I need a flexible way to do this.

As always help gratefuly appreciated!

 

[Excelworld]

I've sent you a PM to answer the above

thanks

Andy

 

[tjgrassi]

Range("A" & Rows.Count).End(xlup).Offset(1).Row
Cells(Rows.Count,1).End(xlup).Offset(1).Row
UsedRange.Rows.Count

How would I make this code select the the first blank cell in column A? Where would the .Select go?

 

[njimack]

How would I make this code select the the first blank cell in column A? Where would the .Select go?

It's rarely necessary to select cells when using VBA. What exactly are you trying to do?

 

[minnieroff12]

that doesn't sound so complex...

Dim LastRow as Long
LastRow = Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1).Row

Sheets("Sheet2").Range("A1").Copy Sheets("Sheet1").Cells(LastRow,1)

I've been trying to use this code, but my skills with vba are lacking. What I'm trying to do is to use a "enter" button connected to a macro. The idea is I enter information into "a4","b4","c4","d4","e4" in my "Entry" worksheet. The information in "c4" and "d4" indicate what columns to use in my worksheet "Data", so if "c4"="restaurant" then "a4".copy into next blank in Column "e" in "Data", "b4".copy into next blank in Column "f" in "Data", "e4".copy into next blank in Column "g" in "Data, and if "d4"="Cash" then "a4".copy into next blank in Column "bb" in "Data", "b4".copy into next blank in Column "bc" in "Data"

"c4" and "d4" have several options, so I'll need a code that is or(if("d4"=This, then "copy to here"),[next if statement]).

That's the best I can do to describe it. Can anyone help? I can provide more information if needed!

Thanks,

Dylan

 

[rnurdani]

Hi,

I`m using this code to fill in the next empty rows with current time :

Sub CommandButton1_Click()

With Sheets("Sheet2")

NextRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

.Range("A" & NextRow) = Now()

End With

End Sub


Using the code above the fillings will start from cell(A2) and it wont stop in certain cell.
The problem I have is that I need to start from certain row and apply it ony to certain range only.

Somebody can help me with this?

Thanks in advance

there are numerous ways of determining the last row or first blank row...

Range("A" & Rows.Count).End(xlup).Offset(1).Row
Cells(Rows.Count,1).End(xlup).Offset(1).Row
UsedRange.Rows.Count

 

[njimack]

Hi,

I`m using this code to fill in the next empty rows with current time :

Sub CommandButton1_Click()

With Sheets("Sheet2")

NextRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

.Range("A" & NextRow) = Now()

End With

End Sub


Using the code above the fillings will start from cell(A2) and it wont stop in certain cell.
The problem I have is that I need to start from certain row and apply it ony to certain range only.

Somebody can help me with this?

Thanks in advance

So once a certain number of cells are populated, what do you want the code to do?

By the way, the code you posted could be simplified to:

Sub CommandButton1_Click()
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = Now()
End Sub

 

[rnurdani]

Thank You.

I am going to record start and stop times for certain resources and activities. So I will have to put them in certain cells. At the end I will get the duration of each activities done by each resources.

This is the solution I have for now :

CODE :

Sub Test()

Dim cell As Range

Dim i As Long

With Worksheets("Sheet2")
For Each cell In .Range("C5:C10")
If cell.Value = "" Then
i = cell.Row
.Range("C" & i).Value = Now()
Exit For
End If

Next cell
End With

End Sub

Should you have better solution please let me know.

So once a certain number of cells are populated, what do you want the code to do?

By the way, the code you posted could be simplified to:

Sub CommandButton1_Click()
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1) = Now()
End Sub

 

[njimack]

You could replace your existing code with a single line...

Sheets("Sheet2").Range("C5:C10").SpecialCells(xlCellTypeBlanks) = Now()

 

[rnurdani]

Thank You, but the code fills range C5:C10 at the same time simultaneously. I`m using a command button and I need only to fill one cell every time I press the command button.

You could replace your existing code with a single line...

Sheets("Sheet2").Range("C5:C10").SpecialCells(xlCellTypeBlanks) = Now()

 

[njimack]

Thank You, but the code fills range C5:C10 at the same time simultaneously. I`m using a command button and I need only to fill one cell every time I press the command button.

My code does exactly the same as your code, just more efficiently. Please explain exactly what you want the code to do.