PDA

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.

PAB
05-10-2016, 07:53 AM
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.