VBA Macro to select area not working

[Browneh89]

Hello all!

I'm having a little trouble with a macro I am attempting to record. The macro is supposed to filter a table of data (removing all blanks rows) then select an area in prep to print. when I record the macro I use Ctrl + Shift + arrow key to select the area which I have used before to write a similar macro (which works perfectly) however this time it won't select past the coloums which filter the data. Does anyone know what could be causing this?

Example of the macro that's failing below;

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveSheet.Range("$B$4:$V$99").AutoFilter Field:=5, Criteria1:="<>"
Range("O1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
End Sub

Thank you in advance for any help you can offer!

 

[JLGWhiz]

If any of the columns on the row have visible data, the arrow method stops and has to be initiated again to continue on. It will only ignore hidden rows or columns, not those which are visible with data. The same rules apply to the range of movement for Ctrl + Shift + Arrow as for End + Arrow.

 

[Browneh89]

Hey,

Yeah I know that, it's why "Range(Selection, Selection.End(xlDown)).Select" is repeated multiple times within the macro but how does that explain it not working?

 

[JLGWhiz]

however this time it won't select past the coloums which filter the data.

This statement from post #1 indicates that it is working as designed, or have I misunderstood what is meant by the statement? It also stops at cells with formulas that = NullString but appear to be blank.

 

[Browneh89]

Yes I'm a little confused as well. So how would I write this so that it detected the last cell with data in and selected the entire range for my table?

 

[Michael M]

Does this help to remove the blank rows ???

Sub MM1()
  With Range("O1", Cells(Rows.Count, "O").End(xlUp))
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("O").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[JLGWhiz]

Yes I'm a little confused as well. So how would I write this so that it detected the last cell with data in and selected the entire range for my table?

Something like this:

ActiveSheet.Range("$B$4:$V$99").AutoFilter Field:=5, Criteria1:="<>"
With ActiveSheet
    Intersect(.Range("B:O"),.UsedRange).SpecialCells(xlCellTypeVisible).copy
    'Code to paste it somewhere goes here
End With

I assume you intend to copy and paste the data to another arear or another sheet to make your table since it is in a filtered range. I note that omit Column A and you include columns P:V in your filter but omit P:V in your selection. Also, Michael M has an alternative to filtering for removing the blanks.

 

[Browneh89]

Hi, I apologize I don't think I explained my issue very well.

So below is the sheet I'm working on I want to be able to select from Cell A1 to N1 and select down to the black highlighted black row at the bottom but whenever I attempt to build a macro using Ctrl - Shift - Down arrow, it gets stuck at row 4 (which has the filters). I only want to select it in prep to print and I don't want to copy the data to another area as after the sheet has been printed out it needs to be filled in manually within the sheet.

I just don't understand why it keeps getting stuck as I have used this to create macros which automatically print sheets before and it's worked totally fine!

I usually select N1 and go down to the bottom of the table than across as the sheet goes further past N1, but I don't require those columns to be printed off.

Final.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					Enter			Total	Real Total		Shift 1	Shift 2	Shift 3
2								0	0		0	0	0
3
4	Date	Time	Arrival	Ref #	ORDER	TYPE	Supplier / Carrier	DELIVER	COUNT	R COUNT	TIME	OFF	LEFT	COMMENTS/ISSUES
5		#VALUE!	0	0		S	#N/A	#N/A
6		#VALUE!	0	0		S	#N/A	#N/A
7		#VALUE!	0	0		S	#N/A	#N/A
8		#VALUE!	0	0		S	#N/A	#N/A
9		#VALUE!	0	0		S	#N/A	#N/A
10		#VALUE!	0	0		S	#N/A	#N/A
11		#VALUE!	0	0		S	#N/A	#N/A
12		#VALUE!	0	0		S	#N/A	#N/A
13		#VALUE!	0	0		S	#N/A	#N/A
14		#VALUE!	0	0		S	#N/A	#N/A
15		#VALUE!	0	0		S	#N/A	#N/A
16		#VALUE!	0	0		S	#N/A	#N/A
17		#VALUE!	0	0		S	#N/A	#N/A
18		#VALUE!	0	0		S	#N/A	#N/A
19		#VALUE!	0	0		S	#N/A	#N/A
20		#VALUE!	0	0		S	#N/A	#N/A
21	-	-	-	-	-	S	-	-	-	-	-	-	-	-
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=SUM(I5:I20)
K2	K2	=SUMIFS($I$5:$I$20,$B$5:$B$20,">="&TIME(22,0,0))+SUMIFS($I$5:$I$20,$B$5:$B$20,"<"&TIME(6,0,0))
L2	L2	=SUMIFS($I$5:$I$20,$B$5:$B$20,">="&TIME(6,0,0),$B$5:$B$20,"<"&TIME(14,0,0))
M2	M2	=SUMIFS($I$5:$I$20,$B$5:$B$20,">="&TIME(14,0,0),$B$5:$B$20,"<"&TIME(22,0,0))
A5:A20	A5	=LEFT(Gate!A2,10)
B5:B20	B5	=RIGHT(Gate!A2,5)+0
C5:C20	C5	=Gate!U2
D5:D20	D5	=Gate!T2
E5:E20	E5	=IFNA(INDEX(Gate!B:B,MATCH(Sheet1!D5,Gate!T:T,0)),"")
G5:G20	G5	=INDEX(Gate!C:C,MATCH(D5,Gate!T:T,0))
H5:H20	H5	=IF(G5=0,INDEX(Gate!E:E,MATCH(D5,Gate!T:T,0)),Sheet1!G5)
I5:I20	I5	=IFNA(INDEX(Gate!J:J,MATCH(Sheet1!D5,Gate!T:T,0)),"")
F5:F21	F5	=IF(LEFT(E5,3)="153","A","S")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:I20	Expression	=$F5="S"	text	YES

 

[JLGWhiz]

Maybe this

Sub t()
ActiveSheet.Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 14).Select
Selection.PrintOut
End Sub

Or just

Sub t()
ActiveSheet.Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 14).PrintOut
End Sub

 

[Browneh89]

Thank you mate this worked perfectly!! Do you know why my original macro did not work?