Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell in column
The code below is intended to paste data onto a worksheet by selecting the
first empty cell in row B. My problem is that I can not be sure what column/row the User will have selected when they run the macro. Is there any way to alter the code below so that it will always start it's search with cell B6 and then go to the first empty cell below that in column B? Sub Paste_Data() Dim BCell, NBCell Dim PasteTo As Range Dim rng Set rng = Cells(ActiveCell.Row, 1) Application.ScreenUpdating = False Application.EnableEvents = False For i = 1 To 65536 If ActiveCell.Value = Empty Then BCell = "B" & CStr(i - 1) NBCell = "B" & CStr(i - 2) GoTo Finished Else Range("B" & CStr(i + 1)).Select End If Next i Finished: rng(1, 2).Select ActiveSheet.Paste Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell in column
Solved it.
"Patrick C. Simonds" wrote in message ... The code below is intended to paste data onto a worksheet by selecting the first empty cell in row B. My problem is that I can not be sure what column/row the User will have selected when they run the macro. Is there any way to alter the code below so that it will always start it's search with cell B6 and then go to the first empty cell below that in column B? Sub Paste_Data() Dim BCell, NBCell Dim PasteTo As Range Dim rng Set rng = Cells(ActiveCell.Row, 1) Application.ScreenUpdating = False Application.EnableEvents = False For i = 1 To 65536 If ActiveCell.Value = Empty Then BCell = "B" & CStr(i - 1) NBCell = "B" & CStr(i - 2) GoTo Finished Else Range("B" & CStr(i + 1)).Select End If Next i Finished: rng(1, 2).Select ActiveSheet.Paste Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell in column
Patrick,
See the code below as an alternative method. You'll need to update the code to fit your needs (i.e. the copy range and paste destination range in particular). As a side note to your code. You can change the numbers in your For Next loop (e.g. i = 7 to Rows.Count) to start the loop in a specified location. Keep in mind though that your BCell = "B" & CStr(i - 1) will evaluate to B0 on the first loop with i = 1 (of course, if the IF statement evaluates True). Since there is no B0 in the spreasheet, this will throw an error if you try to do something like Range(BCell).Select. The same is true for NBCell = "B" & CStr(i - 2). Until you reach i = 3 in your loop, this line will throw an error if you try to do something like Range(NBCell).Select. You don't need the GoTo statement either. Replace the GoTo line with Exit For (i.e. once the condition is true, exit the loop). Best, Matthew Herbert Sub PasteData() Dim rngSearch As Range Dim rngFound As Range Dim rngCopy As Range 'set the range to search for the first blank cell Set rngSearch = Range("B6", "B" & Rows.Count) 'return the first cell of all the blank cells Set rngFound = rngSearch.SpecialCells(xlCellTypeBlanks)(1) If rngFound Is Nothing Then MsgBox "No blank cells in " & rngSearch.Address & "." Exit Sub Else 'copy a cell and paste it 'CHANGE THIS TO BE THE DESIRED CELL TO COPY Set rngCopy = Range("A1") 'CHANGE THIS TO BE THE DESIRED CELL DESTINATION rngCopy.Copy rngFound.Offset(1, 0) End If End Sub "Patrick C. Simonds" wrote: The code below is intended to paste data onto a worksheet by selecting the first empty cell in row B. My problem is that I can not be sure what column/row the User will have selected when they run the macro. Is there any way to alter the code below so that it will always start it's search with cell B6 and then go to the first empty cell below that in column B? Sub Paste_Data() Dim BCell, NBCell Dim PasteTo As Range Dim rng Set rng = Cells(ActiveCell.Row, 1) Application.ScreenUpdating = False Application.EnableEvents = False For i = 1 To 65536 If ActiveCell.Value = Empty Then BCell = "B" & CStr(i - 1) NBCell = "B" & CStr(i - 2) GoTo Finished Else Range("B" & CStr(i + 1)).Select End If Next i Finished: rng(1, 2).Select ActiveSheet.Paste Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find empty cells in a column then append row that empty cell is in | Excel Programming | |||
Fill the cell in column D if column D is empty | Excel Programming | |||
Hiding column if cell in previous column is empty-revised | Excel Programming | |||
Search for first empty cell. | Excel Programming | |||
Loop through column headers to search from column name and get cell range | Excel Programming |