Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub


My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

This macro deletes the rows that are empty.

If you wanted to hide the rows where a cell in a specific column is empty:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if .cells(irow,"C").value = "" then
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

========
But as a user, I would rather see the autofilter arrows and use that. It has
lots of more applications that I could use in other worksheets.

vcff wrote:

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

--

Dave Peterson

Hi Dave

Thanks for the help
Vcff

"Dave Peterson" wrote:

This macro deletes the rows that are empty.

If you wanted to hide the rows where a cell in a specific column is empty:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if .cells(irow,"C").value = "" then
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

========
But as a user, I would rather see the autofilter arrows and use that. It has
lots of more applications that I could use in other worksheets.

vcff wrote:

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

--

Dave Peterson
.

Hi Dave

Need your help again.

the code only check on column C but not range as it also hide all other rows
with data in other columns. It also stop at the last cell under column C with
data, all rows after that still in display.

e.g
A B C D
E F
ProdCode Desc ASD CPA WDS YPL
G10110115 Product 1 18.00 16.00 16.00
G10110320 Product 16 5.00
G10120365 Product 17 6.50 6.50
G10751259 Product 35 60.00
G10762563 Product 40 32.00 30.00 30.50 32.00
G10762564 Product 41
G10762565 Product 42
G10762566 Product 43 5.00
G10762567 Product 44
G10762568 Product 45 2.50
G10802349 Product 150
G10802350 Product 151
G10802351 Product 152

rows after Product 40 still display even if empty.
under column D,E & F although there are pricing for the products purchase
under the 3 companies, it was hidden.

I need to hide the empty rows base on a range of cells (e.g C2:IT450)
Any row from row 2 to 450 w/o pricing to be hidden.

Appreciate your help.

Thanks
Vcff

"Dave Peterson" wrote:

This macro deletes the rows that are empty.

If you wanted to hide the rows where a cell in a specific column is empty:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if .cells(irow,"C").value = "" then
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

========
But as a user, I would rather see the autofilter arrows and use that. It has
lots of more applications that I could use in other worksheets.

vcff wrote:

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

--

Dave Peterson
.

So, can you pick out a column that's always used--maybe column A????

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
'use column A instead.
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if application.counta(.cells(irow,"a").range("c1:it1" )) 0 then
'keep it visible, there's something in it
else
'all those cells are empty, so hide the row
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

vcff wrote:

Hi Dave

Need your help again.

the code only check on column C but not range as it also hide all other rows
with data in other columns. It also stop at the last cell under column C with
data, all rows after that still in display.

e.g
A B C D
E F
ProdCode Desc ASD CPA WDS YPL
G10110115 Product 1 18.00 16.00 16.00
G10110320 Product 16 5.00
G10120365 Product 17 6.50 6.50
G10751259 Product 35 60.00
G10762563 Product 40 32.00 30.00 30.50 32.00
G10762564 Product 41
G10762565 Product 42
G10762566 Product 43 5.00
G10762567 Product 44
G10762568 Product 45 2.50
G10802349 Product 150
G10802350 Product 151
G10802351 Product 152

rows after Product 40 still display even if empty.
under column D,E & F although there are pricing for the products purchase
under the 3 companies, it was hidden.

I need to hide the empty rows base on a range of cells (e.g C2:IT450)
Any row from row 2 to 450 w/o pricing to be hidden.

Appreciate your help.

Thanks
Vcff

"Dave Peterson" wrote:

This macro deletes the rows that are empty.

If you wanted to hide the rows where a cell in a specific column is empty:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if .cells(irow,"C").value = "" then
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

========
But as a user, I would rather see the autofilter arrows and use that. It has
lots of more applications that I could use in other worksheets.

vcff wrote:

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

--

Dave Peterson
.


--

Dave Peterson

Hi Dave

Thanks, I got what I am looking for.

Regards
vcff

"Dave Peterson" wrote:

So, can you pick out a column that's always used--maybe column A????

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
'use column A instead.
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if application.counta(.cells(irow,"a").range("c1:it1" )) 0 then
'keep it visible, there's something in it
else
'all those cells are empty, so hide the row
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

vcff wrote:

Hi Dave

Need your help again.

the code only check on column C but not range as it also hide all other rows
with data in other columns. It also stop at the last cell under column C with
data, all rows after that still in display.

e.g
A B C D
E F
ProdCode Desc ASD CPA WDS YPL
G10110115 Product 1 18.00 16.00 16.00
G10110320 Product 16 5.00
G10120365 Product 17 6.50 6.50
G10751259 Product 35 60.00
G10762563 Product 40 32.00 30.00 30.50 32.00
G10762564 Product 41
G10762565 Product 42
G10762566 Product 43 5.00
G10762567 Product 44
G10762568 Product 45 2.50
G10802349 Product 150
G10802350 Product 151
G10802351 Product 152

rows after Product 40 still display even if empty.
under column D,E & F although there are pricing for the products purchase
under the 3 companies, it was hidden.

I need to hide the empty rows base on a range of cells (e.g C2:IT450)
Any row from row 2 to 450 w/o pricing to be hidden.

Appreciate your help.

Thanks
Vcff

"Dave Peterson" wrote:

This macro deletes the rows that are empty.

If you wanted to hide the rows where a cell in a specific column is empty:

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
if .cells(irow,"C").value = "" then
.Rows(iRow).hidden = true
End If
Next iRow
End With

End Sub

========
But as a user, I would rather see the autofilter arrows and use that. It has
lots of more applications that I could use in other worksheets.

vcff wrote:

Hi Dave

I search and found one of your reply which may do the job but need help
(changes)

Option Explicit
Sub testme02()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myRng As Range

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
'12 columns is C:N
'.resize(1,12) means 1 row by 12 columns
Set myRng = .Cells(iRow, "C").Resize(1, 12)
If Application.CountIf(myRng, 0) = myRng.Cells.Count Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

My range is C2:GR201 and empty cells (not zero).

Can help?

Thanks
vcff

"vcff" wrote:

Hi

Column A & B is the product code.
Row 1 is the customer list
Pricing in cell C2:GR201 (with lots of empty cells_w/o pricing)
To displace rows of product with pricing only (hide rows w/o pricing)

Not using autofilter -I need it to be done in a few steps only

Any help will be appreciated.

Thanks

--

Dave Peterson
.


--

Dave Peterson
.