Find Next Available Cell

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I have a Worksheet with a macro that works in that it does
return a group of values in a row. The last function of
the macro is to highlight the row of figures, copy the
highlighted values and then the macro stops because the
rest I have to do manually.
What I would like to do, is add to the end of the macro, a
command to select the next empty cell in column B, greater
than B15. Do a paste special, values only. This would be
the end of the macro.

I hope someone can assist.

Nick
 
Hi Nick,

Cells(Rows.Count, "B").End(xlUp)(2)

returns the next empty cell in column B.

If you need further assistance, post your code.
 
Norman:
I see a learning experience (for me) here...
What does the (2) on the end of your code line do?
I had to replace it (the (2)) with ".row +1" as follows:

mycell = Cells(Rows.Count, "B").End(xlUp).row +1
Range("B" & mycell).Select

TIA,
 
Hi Jim,

Cells(Rows.Count, "B").End(xlUp)

returns the last non-empty cell in column B.

Appending (2) to the above expression, uses the Item method to return the
cell after the last empty cell in column B.

Instead of using the item method, the offset method could equally well have
been used:

Cells(Rows.Count, "B").End(xlUp).Offset(1)

Chip Pearson's web site hosts an article by Alan Beban might well be of
interest to you:

http://www.cpearson.com/excel/cells.htm
 
If you post your macro you will get suggestions to do what you want
eliminating un-necessary selections which are inefficient and slow down your
macro
 
Hi Don,
The macro I'm using is very long so as you suggested here
it is.
I hope that someone can make it more efficient.
The worksheet can contain up to 20,000 rows. The raw data
comes from another spreadsheet, can this data be imported
into my worksheet using this macro when the file to be
imported is save on my desktop?

Regards
Nick

Sub Calculate()
'
' Calculate Macro
' Macro recorded 28/07/2003 by Nick Ruppert
'

'
Range("B5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=6, Criteria1:="MC1"
Selection.AutoFilter Field:=5, Criteria1:="MCM1"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MCML"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("T5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MESL"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MC2"
Selection.AutoFilter Field:=5, Criteria1:="MCM2"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MC3"
Selection.AutoFilter Field:=5, Criteria1:="MCMS"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MES"
Selection.AutoFilter Field:=5, Criteria1:="MESE"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MS3"
Selection.AutoFilter Field:=5, Criteria1:="MSEL"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("J5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MSES"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("K5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MS1"
Selection.AutoFilter Field:=5, Criteria1:="MSLM"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("L5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MSML"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MC4"
Selection.AutoFilter Field:=5, Criteria1:="MCE1"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MCE2"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("M5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MS2"
Selection.AutoFilter Field:=5, Criteria1:="MSMS"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("N5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MF3"
Selection.AutoFilter Field:=5, Criteria1:="MFET"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("O5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MFEB"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("P5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MF1"
Selection.AutoFilter Field:=5, Criteria1:="MFMT"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Q5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MF2"
Selection.AutoFilter Field:=5, Criteria1:="MFMB"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("U5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5, Criteria1:="MFMS"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("R5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MWY"
Selection.AutoFilter Field:=5, Criteria1:="MWYG"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("S5").Select
Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=6, Criteria1:="MWY"
Selection.AutoFilter Field:=5, Criteria1:="MWYE"
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Sheets("RIAFVC20").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Sheets("Sheet1").Select
Range("B4:U4").Select
Application.CutCopyMode = False
Selection.Copy
mycell = Cells(Rows.Count, "B").End(xlUp).Row + 1
Range("B" & mycell).Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub
 
You shouldn't have to make selections.

Sub shortenit() 'Example
With Sheets("sheet7").Range("A1:G1")
.AutoFilter Field:=3, Criteria1:="2"
sheets("ds").Range("h1") = .Range("c2")'c2 value to h1
.AutoFilter 'clear the autofilter after
End With
End Sub


Sub Calculate()
with Sheets("RIAFVC20").range("yourrangehere")
..AutoFilter Field:=6, Criteria1:="MC1"
..AutoFilter Field:=5, Criteria1:="MCM1"
Sheets("Sheet1").range("yourrangehere")=.range("g1")
..AutoFilter
end with

etc. etc

=====
'you can use this to clear all autofilter on the active sheet.
Sub showall()
On Error GoTo away
ActiveSheet.ShowAllData
away:
End Sub
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

After Macro runs, no cell activated - what am I missing? 2
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Selecting all cells with content using a variable 3
Macro to copy & paste-special-values data to selected worksheets 1
Pasting cell contents into a macro 1
Paste Values in linked Cells Only 4
Follow Up Macro Question 4
Macro Relative Cells 3

Back
Top