Basic Looping Questions

[ShawnExcel]

Here is what I'm attempting to do. I have a list of 200 names, and I have a filter on that shows only 5 of them. I want the code to filter through the list, skip hidden rows, and write those five names (and the info associated with them) to another worksheet. Here is what I have so far. I think my issue is in the "If c.EntireRow.Hidden".... section. It doesn't loop back up and check again if the next row is hidden so if I have two hidden rows, it will still print the second one but ignore the first. I need it to check whether the active cell is hidden another time, after doing it the first time. Here's the code:

Workbooks(UserFormWorkbook).Activate
Range("B4").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
Set C = ActiveCell
If C.EntireRow.Hidden Then ActiveCell.Offset(1, 0).Select
[My code here.....]
Loop
ActiveCell.Offset(1, 0).Select
      Loop

 

[Chihiro]

I wouldn't use loops in that case and don't use "Select".

Just filter the range and then copy and paste.

By default, copy will ignore hidden rows.

Sub Test()
Dim lRow As Long, lRow2 As Long
'Assuming filter is already applied filter

lRow = Range("A" & Rows.Count).End(xlUp).Row
lRow2 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1

'Assuming 1st row is header and already set up
Range("A2:A" & lRow).Copy Sheet2.Cells(lRow2, 1)
End Sub

FYI - To loop through unhidden rows only, just use Range.SpecialCells(xlCellTypeVisible)

 

[ShawnExcel]

Nice! The info is great but I'm not exactly copying and pasting - I'm using the following code...how would I apply your solution to this scenario?

Workbooks(UserFormWorkbook).Activate
Range("B4").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell) And Selection.EntireRow.Hidden = False
Set C = ActiveCell
If C.EntireRow.Hidden Then ActiveCell.Offset(1, 0).Select


IntranetID = ActiveCell.Offset(0, 1).Value
TimeWorked = ActiveCell.Offset(0, 5).Value
UnitsWorked = ActiveCell.Offset(0, 2).Value
ShrinkHours = ActiveCell.Offset(0, 6).Value

'Now paste this sh*t into the main database
Workbooks("Master Database 2016.xlsb").Activate
NextRow = Workbooks("Master Database 2016.xlsb").Worksheets("Master Database 2016").Range("F" & Rows.Count).End(xlUp).Row + 1
Workbooks("Master Database 2016.xlsb").Worksheets("Master Database 2016").Range("F" & NextRow).Value = IntranetID
Workbooks("Master Database 2016.xlsb").Worksheets("Master Database 2016").Range("L" & NextRow).Value = TimeWorked
Workbooks("Master Database 2016.xlsb").Worksheets("Master Database 2016").Range("C" & NextRow).Value = DateWorked
Workbooks("Master Database 2016.xlsb").Worksheets("Master Database 2016").Range("Y" & NextRow).Value = ShrinkHours
        ' Step down 1 row from the present location.
        Workbooks(UserFormWorkbook).Activate
NextRowSubmitted = ThisWorkbook.Worksheets("Submitted").Range("B" & Rows.Count).End(xlUp).Row + 1
ThisWorkbook.Worksheets("Submitted").Range("B" & NextRowSubmitted).Value = DateWorked
ThisWorkbook.Worksheets("Submitted").Range("C" & NextRowSubmitted).Value = IntranetID
ThisWorkbook.Worksheets("Submitted").Range("F" & NextRowSubmitted).Value = TimeWorked
ThisWorkbook.Worksheets("Submitted").Range("E" & NextRowSubmitted).Value = Shrink
ThisWorkbook.Worksheets("Submitted").Range("D" & NextRowSubmitted).Value = UnitsWorked
ThisWorkbook.Worksheets("Submitted").Range("G" & NextRowSubmitted).Value = ShrinkNotes
Worksheets(ThisWorkSheet).Activate
        ActiveCell.Offset(1, 0).Select
      Loop

 

[Chihiro]

Sorry for late reply I was sick yesterday.

Your code looks incomplete. Can you upload sample file with complete code?

My suggestion would be to first commit data to array or scripting.dictionary and then write to destination.

 

[ShawnExcel]

Please see the attached spreadsheet. Each team has 10 people, and right now I manually type each name in for each team. I have one long roster of teams and essentially when "Supervisor 1" hits enter, only his/her employees data gets entered into the database (submitted) tab.

 

[Chihiro]

Ahem. I need password, if you need help.

 

[ShawnExcel]

Chihiro, sorry for the delay! I was actually able to solve this otherwise. Instead of one list of 200 people, I had a list of 12 with and Index & match function, and then used a formula to loop through until column A was = "0" which means there were no more employees listed under that manager. If you still want the password to look at the file I can provide that, but I solved this one myself. Your first posts were helpful in pointing me in the right direction though - thank you!!

 

[Chihiro]

Glad you solved it. No need to provide password.