Find Next Available Cell

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
 
N

Norman Jones

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.
 
J

Jim May

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,
 
N

Norman Jones

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
 
D

Don Guillett

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
 
N

Nick

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
 
D

Don Guillett

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
 
Top