Code to Grab All Used Rows

[Justinian]

How do I write code so that my macro selects all used rows? For example, I have to import data into an Excel workbook every morning and the number of rows are never the same. I need to select all used rows (I do this by manually with ctrl + shift + down arrow) and adjust the row height to 60.00. How do I write this in VBA?

 

[MARK858]

Maybe just….

ActiveSheet.UsedRange.EntireRow.RowHeight = 60

or even

ActiveSheet.UsedRange.RowHeight = 60

 

[DanteAmor]

To learn a little about macros, you can activate the macro recorder and perform the steps, the result will be something like this:

Sub Macro1()
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.RowHeight = 60
End Sub

You can simplify trial and error to get to this:

Sub Macro2()
    Range("A1", Range("A1").End(xlDown)).Select
    Selection.RowHeight = 60
End Sub

And so:

Sub Macro3()
    Range("A1", Range("A1").End(xlDown)).RowHeight = 60
End Sub

 

[MARK858]

@Justinian, it should also be pointed out that "ctrl + shift + down arrow" doesn't actually select all the used cells, it selects down to the first blank cell and so if you had a blank cell half way down your column then it would only select until there.

 

[Justinian]

To learn a little about macros, you can activate the macro recorder and perform the steps, the result will be something like this:

Sub Macro1()
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.RowHeight = 60
End Sub

You can simplify trial and error to get to this:

Sub Macro2()
    Range("A1", Range("A1").End(xlDown)).Select
    Selection.RowHeight = 60
End Sub

And so:

Sub Macro3()
    Range("A1", Range("A1").End(xlDown)).RowHeight = 60
End Sub

Ok, that works. Thank you.

 

[Justinian]

How does this differ from this?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 60[/FONT]

 

[Justinian]

Maybe just….

ActiveSheet.UsedRange.EntireRow.RowHeight = 60

or even

ActiveSheet.UsedRange.RowHeight = 60

How does that differ from this?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 60[/FONT]

 

[MARK858]

How does that differ from this?

Set up 2 sheets as per the below and run one of the codes I posted on one sheet and the xldown code on the other Obviously select A1 for the xldown code before using the code,

Book1
	A	B	C	D
1	aaa
2	aaa
3	aaa
4	aaa
5	aaa
6	aaa
7	aaa
8	aaa
9	aaa
10
11
12
13
14	aaa
15	aaa
16	aaa
17	aaa
18	aaa
19	aaa
20	aaa
21	aaa
22	aaa
23	aaa
24	aaa
25	aaa
Sheet4

 

[Justinian]

Duh, I should have been able to figure that out! Haha.

Thank you all for your help!

 

[MARK858]

Your welcome, there are a few other options but they can wait until another day (unless there is ever a chance that there isn't any data in Row 1, if that can be the case then you might have an issue with UsedRange (also if you find you have any issues with it retaining a memory of the last row) then post back).