View Full Version : Select number of rows based cell value
emina002
05-10-2016, 07:06 AM
Hi,
I am having a problem on how to select rows based on numeric value written on a cell, I have 2 sheet, on other sheet is my data.If header column is on row 2, supposed to be the line items start on row 3,on the other sheet is the numeric value on cell F9,
For example I entered 3 as numbers so from row 3 to row 5 must be selected(because 3 rows must be selected),how can I do that on VBA?thanks for helping.
Hi emina002, welcome to the board :yes .
Do you mean something like this...
Sub Sel_Rws()
Rows("3:5").Select
End Sub
I hope this helps!
emina002
05-10-2016, 08:17 AM
Thanks for noticing my question,what I want is if the user input "3" on F9 cell, will select only 3 rows from 3 to 5, if I enter "4" it will select rows from 3 to 6(4 rows to be selected), selecting rows will be based on user input.
mfreem02
05-10-2016, 08:36 AM
Building on PAB's reply, would it be:
Sub Sel_Rws()
dim RwCnt as integer
RwCnt = OtherSheet.Range("F9").value
Rows("3:" & RwCnt).Select
End Sub
emina002
05-10-2016, 10:16 AM
Ok I edit the code you suggested to meet my exact requirement, I filtered first all "No Fill" color, using the modified code below, it only select row 3(A3:W3) and perform fill color of yellow. I already used special visible cells but then it failed:(
Dim ctr As IntegerSheet4.Activate
'
ActiveSheet.Range("$A$2:$W$1000").AutoFilter Field:=10, Operator:= _
xlFilterNoFill
'Range("A2").Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
Dim RwCnt As Integer
RwCnt = Sheet5.Range("F9").Value + 1
Range("A2:" & "W" & RwCnt).Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
Selection.SpecialCells(xlCellTypeVisible).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
emina002
05-11-2016, 06:17 AM
Ok above code will be helpful but my problem is if the macro perform filtered data with no fill color, it will select only number of rows written on cell F9. That is what I need. I am getting error on above code because it is literally select row 3 to 5 and if this was hidden because of filtering I encounter error.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.