Select a Range Start in Middle goto the Last filled Row

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Range("K3:K" & Cells(Rows.count, 1).End(xlUp).Row
This gets me all the used rows in column K

Now I go over to Column G
Range("G3").End(xlDown).Select
This gets me to the first filled row in Column G

How do I start there and Include a Range for Printing
It will be 2 Rows More than what was found in Column K

Range(ActiveCell:End(xlDown)).Select

123 would be {End}{D}.{End}{D}~

Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Your post is unclear because you are defining your K column's last row based on the last non-empty row in column A. It is also unclear what column(s) you want to include in the print range (A:K, G:K, or K:K - - you make that a mystery).

Here is code to give you options depending on what you are actually trying to accomplish. You can only select one range setting so be sure to delete the other two:

Sub Test1()

Dim FirstRow As Long, LastRow As Long, PrintRange As Range
FirstRow = Range("G3").End(xlDown).Row
LastRow = Cells(Rows.Count, 11).End(xlUp).Row + 2

'It's up to you how you define the print range because your post is unclear:

'If you want to only print column K
Set PrintRange = Range(Cells(FirstRow, 11), Cells(LastRow, 11))

'Or if you want to print from column G to column K
Set PrintRange = Range(Cells(FirstRow, 7), Cells(LastRow, 11))

'Or, if you want to print from column A to column K because your original code referenced column A to define the last row in K.
Set PrintRange = Range(Cells(FirstRow, 1), Cells(LastRow, 11))

'Now act on the PrintRange
PrintRange.PrintOut

End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
want2b said:
Range("K3:K" & Cells(Rows.count, 1).End(xlUp).Row
This gets me all the used rows in column K

Now I go over to Column G
Range("G3").End(xlDown).Select
This gets me to the first filled row in Column G

How do I start there and Include a Range for Printing
It will be 2 Rows More than what was found in Column K

Range(ActiveCell:End(xlDown)).Select

123 would be {End}{D}.{End}{D}~

Thanks
Hi want2b:

Welcome to MrExcel Board!

You can select your print range as per your specification using the following ....
Code:
Range("K3:K"&Range("K"&rows.Count).End(xlUp)(3).Row).offset(0,-4).resize(,5).select
Would this do?
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Sorry for not knowing how to state question.

I have sorted blanks to the top of a column

there is data all around but I just want to print rows in this one column that has data.

The starting point is the first filled row below row 3

the ending point is the last filled row before a skip as there is a sum
which is the last row I want printed. This is two rows more than
the data on both sides.

then there is a few blank rows then more data that I do not want to include.

Going to the bottom and coming up will not work because there is data
below that I do not want.

Starting at row 3 end down gets to the first non blank

Then another end down should get to the last non blank

I just can't figure out how to do it in Excel

In 123 it would be {end}{down} to get to the first non blank
then . to lock the range
then {end}{down} to get to the last non blank
then the tilder ~ tp select the range.

Thanks for the replies It's hard to find the answer when I don't
know how to ask the question.
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89

ADVERTISEMENT

This is really simple in Lotus

Goto G3 which is blank
end down to first filled row
Start the Range
end down to get to the last filled row
Stop the Range
the next couple of rows in column G are blank

Then there is more filled rows in Column G That are NOT to be included

Can anyone at least tell me how to ask this?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
want2b said:
This is really simple in Lotus

Goto G3 which is blank
end down to first filled row
Start the Range
end down to get to the last filled row
Stop the Range
the next couple of rows in column G are blank

Then there is more filled rows in Column G That are NOT to be included

Can anyone at least tell me how to ask this?
Hi want2b:

If this is what you have ...
y040128h1a.xls
FGHI
1
2
3<--blankperOPspec
4
5
6firstEntryFirstcontiguous blockofentries afterblankcellG3 selectedonexecuting yWant2bmacro
7a
8b
9c
101
112
123
13k
14p
15want2b
16
17morehere
18and
19soon
Sheet13


Then try this ...
Code:
Sub yWant2b()
    Range("G3").Select
    ActiveCell.End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
Does it work for you now?
 

want2b

Board Regular
Joined
Jan 27, 2004
Messages
89
Yes Sir!
That does the trick.
Thank you very much.
I searched and searched
Then couldn't even figure how to ask the question.

Thank you for helping a real dummy that even those books can't help.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi want2b:

So, finally you have what you were looking for. Don't be hard on yourself -- the thing that was confusing about your request was that you were going to column K and making some selections, while you wanted to select a block of entries in column G. And of course later on your description of what you were looking for was very clear -- and don't forget that it is the interaction on the board that can draw us to clearer thinking.

Now let us keep EXCELing!
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top