software

Would like the macro to read:
Go to first blank cell in column, then copy the information from the row
above into the first blank row, without specifing the row number in the
macro, so that it remains flexible, thank you.


Hi Trixie,

I'm assuming you have some knowledge of using macros from the wording
of your question.

Try the below code (adapted from Bob Phillip's post,
www.excelforum.com/showthread.php?t=500529):

Sub CopyToFirstBlankRow()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(rows.Count, quot;Aquot;).End(xlUp).Row
ActiveSheet.Range(quot;Aquot; amp; LastRow).EntireRow.Copy ActiveSheet.Range(quot;Aquot;
_
amp; LastRow 1)
End Sub

fyi, Bob didn't include quot;activesheet.quot; on his post but it appears that
I need it in my setup of Excel (not sure why?).

The above copies the entire row, if you only want some info copied (eg
col's A to L) try changing the copy line to something like:
ActiveSheet.Range(quot;Aquot; amp; LastRow amp; quot;:Lquot; amp; LastRow).Copy _
ActiveSheet.Range(quot;Aquot; amp; LastRow 1)

hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=524172Thank you so much for your reply, it works very well and is much appreciated.

quot;broro183quot; wrote:

gt;
gt; Hi Trixie,
gt;
gt; I'm assuming you have some knowledge of using macros from the wording
gt; of your question.
gt;
gt; Try the below code (adapted from Bob Phillip's post,
gt; www.excelforum.com/showthread.php?t=500529):
gt;
gt; Sub CopyToFirstBlankRow()
gt; Dim LastRow As Long
gt; LastRow = ActiveSheet.Cells(rows.Count, quot;Aquot;).End(xlUp).Row
gt; ActiveSheet.Range(quot;Aquot; amp; LastRow).EntireRow.Copy ActiveSheet.Range(quot;Aquot;
gt; _
gt; amp; LastRow 1)
gt; End Sub
gt;
gt; fyi, Bob didn't include quot;activesheet.quot; on his post but it appears that
gt; I need it in my setup of Excel (not sure why?).
gt;
gt; The above copies the entire row, if you only want some info copied (eg
gt; col's A to L) try changing the copy line to something like:
gt; ActiveSheet.Range(quot;Aquot; amp; LastRow amp; quot;:Lquot; amp; LastRow).Copy _
gt; ActiveSheet.Range(quot;Aquot; amp; LastRow 1)
gt;
gt; hth
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=524172
gt;
gt;


Hi Trixie,
Thanks for the feedback, pleased I could help.

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=524172No problem thank you for answering. I am a beginner at this but am lucky
enough to know exactly what I require. I am looking for other basic codes
too e.g. highlight the row the cursor is in (and spell check, or find
specific words)... Would you happen to know where I can find the basics? I
struggle with the command - in the active cell / row. Also, the macros made
the spreadsheet rather large is there any way I can recude the size? Thanks
heaps.

quot;broro183quot; wrote:

gt;
gt; Hi Trixie,
gt; Thanks for the feedback, pleased I could help.
gt;
gt; Rob Brockett
gt; NZ
gt; Always learning amp; the best way to learn is to experience...
gt;
gt;
gt; --
gt; broro183
gt; ------------------------------------------------------------------------
gt; broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
gt; View this thread: www.excelforum.com/showthread...hreadid=524172
gt;
gt;


This post was EXACTLY what I was looking for.. Although I am pretty new
to excel and am having some problems.

I did it to run but it is not going to the first blank cell, is it
going to the first blank cell at the bottom and copying and filling.
Is there a way to change that?

Basically what I have and need to do is..
Row1 = quot;Blahquot;
Then the next 500 rows under that column are empty.

Row 501 = quot;SomethingElsequot; Then blank rows until whatever..

I need something to fill in 2-500 with quot;Blahquot;

Then 502-whatever with quot;SomethingElsequot;

I hope that takes sense, from reading the post about this.. That is
what it is suppose to do but I can't get it to work. I just jumps to
the very bottom and starts and skips all the blank rows above it.

Any help would be so greatly appreciated, I have been messing with this
for hours and I need to get it done tonight..--
kevinz
------------------------------------------------------------------------
kevinz's Profile: www.excelforum.com/member.php...oamp;userid=32759
View this thread: www.excelforum.com/showthread...hreadid=524172
Hi
quot; I am a beginner...quot; Me too, but I love the challenge!

To highlight the row the cursor is in, have a look at:
excelforum.com/showthread.php?t=520284

For spellcheck/find, I'd suggest starting by recording a macro of your
actions so that you have some code to modify/adapt.

quot; I struggle with the command - in the active cell / row. quot;
With regards to what?

The macro below shouldn't have made much of a difference to file size.
How large is quot;rather largequot;?
Try going to the last row, selecting all the rows underneath it amp; then
using the menus edit-delete (not the delete key as this just clears the
cell contents) and repeating this with the empty columns at the right of
your sheet.Also, the link below seems to have quite a few suggestions re
file size:
www.ozgrid.com/forum/showthread.php?p=88327

Some other sources of info for optimising macros/worksheets:
www.mvps.org/dmcritchie/excel/getstarted.htm (good for
beginners)
www.cpearson.com/excel/optimize.htm
www.ozgrid.com/VBA/SpeedingUpVBACode.htm
www.decisionmodels.com

hth
Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...

Trixie Wrote:
gt; No problem thank you for answering. I am a beginner at this but am
gt; lucky
gt; enough to know exactly what I require. I am looking for other basic
gt; codes
gt; too e.g. highlight the row the cursor is in (and spell check, or find
gt; specific words)... Would you happen to know where I can find the
gt; basics? I
gt; struggle with the command - in the active cell / row. Also, the macros
gt; made
gt; the spreadsheet rather large is there any way I can recude the size?
gt; Thanks
gt; heaps.
gt;--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=524172
hi Kevinz,

How many rows are in the spreadsheet?
If there are not many rows amp; it only has to be done once the quickest
approach would have been to do it manually!
In terms of quot;teaching a man to fishquot;, the manual approach could be
recorded into a macro amp; adapted from there - as below:
recorded code:
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range(quot;A1:A14quot;).Select
ActiveSheet.Paste

This can be adapted as follows but b/c I can't tell if you want the
complete row copied in rows 2 to 500 or just the info in some cells eg
A2:IV500 or A2:A500 I'll show you both...

Sub *CopyingBlanksBelowPopulatedCells*()
Dim bottomOfLastCopiedSection As Long
Repeat:
bottomOfLastCopiedSection = Selection.End(xlDown).Row
If bottomOfLastCopiedSection = rows.Count Then
MsgBox quot;all copying except last section complete. Please copy this
section manually.quot;
Exit Sub
Else
ActiveCell.Range(quot;A1quot;).Copy Range(Selection,
Selection.End(xlDown).Offset(RowOffset:=-1))
Selection.End(xlDown).Activate
End If
GoTo Repeat
End Sub

Sub *CopyingBlanksBelowPopulatedRows*()
Dim bottomOfLastCopiedSection As Long
Repeat:
bottomOfLastCopiedSection = Selection.End(xlDown).Row
If bottomOfLastCopiedSection = rows.Count Then
MsgBox quot;all copying except last section complete. Please copy this
section manually.quot;
Exit Sub
Else
ActiveCell.Range(quot;A1quot;).EntireRow.Copy Range(Selection,
Selection.End(xlDown).Offset(RowOffset:=-1))
Selection.End(xlDown).Activate
End If
GoTo Repeat
End Sub

There will be tidy ways of doing this but I don't know them amp; I can't
tell from your post how to recognise the end of used area which is why
I have finished it with a message to do the last section manually.
(fyi, a solution will probably involve checking the intersection of the
used range with the active cell).

Rob Brockett
NZ
Always learning amp; the best way to learn is to experience...kevinz Wrote:
gt; This post was EXACTLY what I was looking for.. Although I am pretty new
gt; to excel and am having some problems.
gt;
gt; I did it to run but it is not going to the first blank cell, is it
gt; going to the first blank cell at the bottom and copying and filling.
gt; Is there a way to change that?
gt;
gt; Basically what I have and need to do is..
gt; Row1 = quot;Blahquot;
gt; Then the next 500 rows under that column are empty.
gt;
gt; Row 501 = quot;SomethingElsequot; Then blank rows until whatever..
gt;
gt; I need something to fill in 2-500 with quot;Blahquot;
gt;
gt; Then 502-whatever with quot;SomethingElsequot;
gt;
gt; I hope that takes sense, from reading the post about this.. That is
gt; what it is suppose to do but I can't get it to work. I just jumps to
gt; the very bottom and starts and skips all the blank rows above it.
gt;
gt; Any help would be so greatly appreciated, I have been messing with this
gt; for hours and I need to get it done tonight..--
broro183
------------------------------------------------------------------------
broro183's Profile: www.excelforum.com/member.php...oamp;userid=30068
View this thread: www.excelforum.com/showthread...hreadid=524172